← Meta Interview Insights

Meta·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
May 2026

Summary

Meta DS interview with a SQL question centered on ad performance metrics. Pretty standard stuff for the role, but the details matter a lot here.

Questions Asked (1)

Q1

Given three tables (ads, impressions/clicks, and conversions), write a SQL query that returns impressions, clicks, conversions, CTR, and conversion rate for each ad and calendar date over the last 7 days. Order results by date and ad_id.

Product Analytics & MetricsData Modeling
Author's notes

The join logic is where I spent most of my mental energy.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the table schemas and grain (e.g., each impression/click/conversion event has ad_id and timestamp). Then design a query that aggregates each event type to ad_id and date, joins them on ad_id and date, and computes CTR and conversion rate with proper handling of zero denominators. Use a date filter for the last 7 days and order by date and ad_id.

Pro tip: Always guard against division by zero using NULLIF or CASE, and consider whether the last 7 days should be based on the current date or the max date in the data—clarify this with the interviewer to show attention to detail.

1. Clarify schema and grain

Ask about the table structures: what columns exist, how events are recorded (e.g., one row per impression/click/conversion), and whether timestamps are in UTC. Confirm the definition of 'last 7 days' (e.g., relative to current date or max date in data).

2. Aggregate each event type

Write subqueries or CTEs to count impressions, clicks, and conversions per ad_id and calendar date. Ensure you filter each to the last 7 days to reduce data volume.

3. Join aggregated metrics

Full outer join the aggregated tables on ad_id and date to ensure ads with only some event types are included. Use COALESCE to replace NULLs with 0 for counts.

4. Compute derived metrics

Calculate CTR as clicks / impressions and conversion rate as conversions / clicks (or impressions, depending on definition). Use NULLIF or CASE to avoid division by zero, returning NULL or 0 as appropriate.

5. Order and format results

Order the final result by date and ad_id. Select only the required columns: date, ad_id, impressions, clicks, conversions, CTR, and conversion rate.

Key Points to Mention

  • Use of CTEs or subqueries to pre-aggregate each event type before joining, which improves readability and performance.
  • Handling of zero denominators in CTR and conversion rate calculations (e.g., NULLIF or CASE).
  • Definition of 'last 7 days' and how to filter dates (e.g., WHERE date >= CURRENT_DATE - INTERVAL '7 days' or based on max date).
  • Choice of join type (e.g., FULL OUTER JOIN) to include ads with no clicks or conversions.
  • Use of COALESCE to replace NULL counts with 0.
  • Ordering by date and ad_id as specified, and ensuring the output includes all required metrics.

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