Quick Overview

This question evaluates a candidate's proficiency in SQL data manipulation and analytics, including aggregation, joins, date-range filtering, handling missing-period values, distinct counts, and proportional calculations within the Data Manipulation (SQL/Python) domain.

Write SQL for revenue and advertiser analyses

Company: Meta

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Onsite

Use the schema below and ANSI SQL. Treat “today” as 2025-09-01. Schema: - active_ads(date DATE, ad_id INT, advertiser_id INT, creation_source VARCHAR, revenue DECIMAL(12,2)) - advertiser_info(advertiser_id INT, advertiser_name VARCHAR, advertiser_country VARCHAR) Sample rows (minimal, for clarity): active_ads date | ad_id | advertiser_id | creation_source | revenue 2025-08-30 | 101 | 1 | web | 120.00 2025-08-30 | 102 | 1 | api | 80.00 2025-08-31 | 103 | 2 | web | 0.00 2025-09-01 | 104 | 3 | mobile | 200.00 2025-09-01 | 105 | 2 | web | 50.00 advertiser_info advertiser_id | advertiser_name | advertiser_country 1 | Acme | US 2 | Globex | CA 3 | Initech | US Tasks: 1) Daily revenue by creation_source: Return date, creation_source, daily_revenue where daily_revenue = SUM(revenue) over all ads for that source on that date. Order by date, creation_source. Include only dates present in active_ads. 2) Ten countries with the fewest active advertisers in the last 30 days: Define an “active advertiser” as one with at least one active_ads row with revenue > 0 and date between 2025-08-03 and 2025-09-01 inclusive. Return the 10 countries (from advertiser_info) with the smallest count of distinct active advertisers, including countries with zero active advertisers (show count 0). Break ties by advertiser_country alphabetically. Output columns: advertiser_country, active_advertiser_count. 3) Growth proportion by creation_source: For each creation_source, compute the proportion of advertisers whose spend in 2025-01-01..2025-09-01 exceeds their spend in 2024-01-01..2024-09-01 by at least 1000. Denominator = number of advertisers who had any spend (>0) in either period for that same creation_source. Output: creation_source, num_grew_by_1000, denom, proportion (rounded to 4 decimals). Provide a single query (CTEs allowed) that handles advertisers present in only one of the two periods by treating missing-period spend as 0.

Overview: This question evaluates a candidate's proficiency in SQL data manipulation and analytics, including aggregation, joins, date-range filtering, handling missing-period values, distinct counts, and proportional calculations within the Data Manipulation (SQL/Python) domain.

Read the full Meta Data Scientist interview experience this question came from

Daily Revenue by Creation Source

You are given two tables: active_ads and advertiser_info. Table: active_ads - date (DATE): calendar date of the ad impression. - ad_id (INT): unique identifier for the ad. - advertiser_id (INT): identifier for the advertiser that owns the ad. - creation_source (VARCHAR): channel where the ad was created (e.g., 'web', 'api', 'mobile'). - revenue (DECIMAL): revenue generated by that ad on that date. Table: advertiser_info - advertiser_id (INT): unique identifier for the advertiser. - advertiser_name (VARCHAR): name of the advertiser. - advertiser_country (VARCHAR): country of the advertiser. Task: Return the daily revenue by creation_source. For each (date, creation_source) pair, compute daily_revenue = SUM(revenue) over all ads for that creation_source on that date. Return columns: - date - creation_source - daily_revenue Include only dates that appear in active_ads. Order the result by date ascending, then creation_source ascending.

Tables

active_ads(date DATE, ad_id INT, advertiser_id INT, creation_source VARCHAR(20), revenue DECIMAL(12,2))

advertiser_info(advertiser_id INT, advertiser_name VARCHAR(100), advertiser_country VARCHAR(50))

Hints

  1. Group by both date and creation_source.
  2. Use SUM(revenue) and order by date then creation_source.

Countries with Fewest Active Advertisers in a 30-Day Window

Using the same active_ads and advertiser_info tables as described, define an "active advertiser" as one that has at least one row in active_ads with revenue > 0 and date between 2025-05-03 and 2025-06-01 inclusive. Task: Find the 10 advertiser countries (from advertiser_info) with the smallest number of distinct active advertisers in that window. Include countries that have zero active advertisers in this period (show active_advertiser_count = 0). Output columns: - advertiser_country - active_advertiser_count (the count of distinct advertisers from that country that are active in the given window) Order the result by active_advertiser_count ascending, and for ties order alphabetically by advertiser_country. Return only the first 10 rows after sorting.

Tables

active_ads(date DATE, ad_id INT, advertiser_id INT, creation_source VARCHAR(20), revenue DECIMAL(12,2))

advertiser_info(advertiser_id INT, advertiser_name VARCHAR(100), advertiser_country VARCHAR(50))

Hints

  1. First find the distinct active advertiser_ids in the date window using a CTE or subquery.
  2. Left join advertiser_info to the active set and count DISTINCT advertiser_id per country, then order by the count and country and limit to 10.

Growth Proportion by Creation Source Across Years

Using the same active_ads and advertiser_info tables, analyze advertiser spend by creation_source across two periods: - Period 1: 2024-01-01 to 2024-09-01 (inclusive) - Period 2: 2025-01-01 to 2025-09-01 (inclusive) For each advertiser and creation_source, define: - revenue_2024 = total revenue in Period 1 for that advertiser and creation_source. - revenue_2025 = total revenue in Period 2 for that advertiser and creation_source. An advertiser is considered to have "grown by at least 1000" for a given creation_source if: - revenue_2025 - revenue_2024 >= 1000 For each creation_source, compute: - num_grew_by_1000: number of advertisers whose spend grew by at least 1000 for that creation_source. - denom: number of advertisers who had any positive spend (revenue > 0) in EITHER period for that creation_source. - proportion: num_grew_by_1000 / denom, rounded to 4 decimal places. Advertisers that are present in only one of the two periods for a given creation_source should be treated as having 0 revenue in the missing period. Task: Write a single SQL query (you may use CTEs) that returns, for each creation_source: - creation_source - num_grew_by_1000 - denom - proportion (rounded to 4 decimal places) Order the result by creation_source ascending.

Tables

active_ads(date DATE, ad_id INT, advertiser_id INT, creation_source VARCHAR(20), revenue DECIMAL(12,2))

advertiser_info(advertiser_id INT, advertiser_name VARCHAR(100), advertiser_country VARCHAR(50))

Hints

  1. First aggregate revenue per advertiser_id, creation_source, and period using conditional SUM with CASE on the date ranges.
  2. In a second aggregation, per creation_source, count advertisers whose revenue_2025 - revenue_2024 >= 1000 and divide by the number with positive spend in either period; use CASE to handle missing periods as 0.

Loading coding console...