← Meta Interview Insights

Meta·Data Scientist·Technical Phone Screen·Senior

Senior
May 2026

Summary

SQL round at Meta for a DS role, one question but it had a few layers to it. The core was straightforward but the active ad definition tripped me up a bit.

Questions Asked (1)

Q1

Given an ads platform with tables for ads, impressions, and revenue events, write a SQL query that returns daily active ad revenue broken down by creation source. An ad counts as active on a day if it received at least one impression that day, and revenue should only be summed for those active ads on that same day. Include distinct active ad count and total revenue per day per source, ordered by date and source.

Product Analytics & MetricsData Modeling
Author's notes

The join structure took me a second to get right.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, clarify the schema and definitions: identify the ads table with creation source, impressions table with ad_id and date, and revenue events table with ad_id, date, and revenue. Then, compute daily active ads by joining impressions to ads and grouping by date and source, and finally join revenue events to these active ads on the same day to sum revenue and count distinct active ads.

Pro tip: Explicitly state your assumptions about the schema (e.g., column names, date fields) and edge cases (e.g., multiple impressions per ad per day, revenue events without impressions) before writing the query—this shows you think like a data scientist who validates data before analysis.

1. Clarify schema and definitions

Ask or state assumptions about table structures: ads (ad_id, creation_source), impressions (ad_id, impression_date), revenue_events (ad_id, revenue_date, revenue_amount). Define 'active ad' as having at least one impression on that day.

2. Identify daily active ads

Write a subquery or CTE that selects distinct ad_id and date from impressions, then join to ads to get creation_source. This yields one row per active ad per day per source.

3. Aggregate active ad counts

Group the active ads by date and creation_source to count distinct active ads per day per source.

4. Join revenue and sum

Join revenue_events to the active ads on both ad_id and date (revenue_date = impression_date), then sum revenue per day per source. Ensure only revenue for active ads on that same day is included.

5. Combine and order results

Combine the active ad count and revenue sum into a final query, grouping by date and source, and order by date and source ascending.

Key Points to Mention

  • Use of DISTINCT or GROUP BY to ensure each ad is counted once per day even with multiple impressions.
  • Join condition for revenue must include both ad_id and date to enforce same-day revenue attribution.
  • Handling of ads with no impressions or no revenue (e.g., LEFT JOIN vs INNER JOIN) and how that affects active ad definition.
  • Potential need to filter revenue events to only those matching active ads (e.g., using EXISTS or INNER JOIN).
  • Efficiency considerations: indexing on ad_id and date, and avoiding unnecessary joins.
  • Clarifying whether 'creation source' is a column in ads or requires a join to another table.

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