Meta·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

May 2026

Summary

Meta DS technical screen, two SQL questions back to back focused on ad event data. Nothing too wild conceptually but the deduplication and division-by-zero edge cases were clearly the point of the whole thing.

Questions Asked (2)

Write a SQL query to compute the overall click-through rate (clicks divided by impressions) for the last week, deduplicating identical event logs and handling the case where impressions could be zero.

Product Analytics & MetricsData Modeling

Author's notes

The dedup part is what trips people up.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and definitions (e.g., what constitutes an event, time zone, deduplication key). Then write a SQL query that filters events from the last week, deduplicates using DISTINCT or GROUP BY, and computes CTR as SUM(clicks)/NULLIF(SUM(impressions),0).

Pro tip: Always use NULLIF or a CASE statement to avoid division by zero, and explicitly state your assumptions about the data (e.g., event uniqueness, time window). This shows attention to detail and robustness.

1. Clarify requirements and schema

Ask about the table structure, event types (click/impression), timestamp column, and how to define 'last week' (e.g., last 7 days from today). Confirm the deduplication key (e.g., event_id or all columns).

2. Filter events for the last week

Use a WHERE clause on the timestamp column to select events from the last 7 days, ensuring the time zone is consistent.

3. Deduplicate event logs

Apply DISTINCT or GROUP BY on the relevant columns (e.g., event_id, user_id, timestamp, event_type) to remove identical duplicate records.

4. Aggregate clicks and impressions

Use conditional aggregation (e.g., SUM(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END)) to count clicks and impressions separately.

5. Compute CTR with zero handling

Calculate CTR as clicks divided by impressions, using NULLIF(impressions, 0) or a CASE statement to return NULL or 0 when impressions are zero.

Key Points to Mention

Deduplication strategy: use DISTINCT or GROUP BY on event_id or all columns to remove exact duplicates.
Time window definition: clarify 'last week' as last 7 days or previous calendar week, and handle time zones.
Conditional aggregation: use SUM(CASE WHEN ...) to count clicks and impressions in one pass.
Zero impressions handling: use NULLIF or CASE to avoid division by zero, and decide whether to return NULL or 0.
Performance considerations: filter early, use indexes on timestamp and event_type if available.
Edge cases: no events in the period, all impressions but no clicks, or duplicate events with different timestamps.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Extend your CTR query to break down click-through rate by campaign type, joining to a campaigns table, and still handling deduplication and zero-impression edge cases.

Product Analytics & MetricsData Modeling

Author's notes

Basically the same dedup CTE reused, then a LEFT JOIN from campaigns to the aggregated results so you don't accidentally drop campaign types with no events.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by outlining the base CTR query with deduplication logic, then extend it by joining to the campaigns table on campaign_id. Ensure the join preserves all events and handles zero-impression cases by using a LEFT JOIN and filtering or aggregating appropriately. Finally, group by campaign type and compute CTR with proper null handling.

Pro tip: Explicitly discuss how you handle zero-impression edge cases—e.g., using NULLIF or CASE statements to avoid division by zero—and mention that you validate the join doesn't introduce duplicates. This shows attention to data quality and robustness.

1. Define base CTR query with deduplication

Write a subquery to deduplicate events (e.g., by user_id and timestamp) and compute clicks and impressions per campaign. Ensure the deduplication logic is clear and justified.

2. Join to campaigns table

Use a LEFT JOIN to bring in campaign_type from the campaigns table, ensuring that all campaigns in the event data are retained even if missing from the campaigns table.

3. Handle zero-impression edge cases

Use conditional logic (e.g., CASE WHEN impressions = 0 THEN NULL ELSE clicks/impressions END) to avoid division by zero and represent CTR as NULL or 0 as appropriate.

4. Aggregate by campaign type

Group by campaign_type and compute the overall CTR, summing clicks and impressions across campaigns within each type before dividing.

5. Validate and present results

Check for any anomalies (e.g., NULL campaign_type) and explain how you would present the results, including any caveats about data quality.

Key Points to Mention

Deduplication strategy: use of ROW_NUMBER() or DISTINCT on event-level data to avoid double-counting clicks/impressions.
Join type: LEFT JOIN to preserve all events, and handling of missing campaign_type (e.g., COALESCE to 'unknown').
Zero-impression handling: use of NULLIF or CASE to prevent division by zero, and decision on whether to return NULL or 0.
Aggregation order: sum clicks and impressions first, then divide, to avoid averaging ratios.
Data validation: checking for duplicate rows after join, and ensuring campaign_type is correctly mapped.
Performance considerations: indexing on join keys and filtering before aggregation if possible.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.