Coding questions · Meta
Write SQL for revenue and advertiser analyses
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.
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:
- 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.
- 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.
- 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.
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.
Table active_ads
| column | type | constraints |
|---|---|---|
| date | DATE | NOT NULL |
| ad_id | INT | PRIMARY KEY |
| advertiser_id | INT | NOT NULL |
| creation_source | VARCHAR(20) | NOT NULL |
| revenue | DECIMAL(12,2) | NOT NULL |
Sample data:
| ad_id | advertiser_id | creation_source | date | revenue |
|---|---|---|---|---|
| 1001 | 1 | web | 2024-02-10 | 500 |
| 1002 | 4 | web | 2024-04-15 | 400 |
| 1003 | 1 | api | 2024-05-20 | 300 |
| 1004 | 5 | api | 2024-06-10 | 200 |
| 1005 | 2 | mobile | 2024-07-01 | 100 |
| 1006 | 3 | mobile | 2024-07-10 | 600 |
| 1007 | 8 | mobile | 2024-07-20 | 1200 |
| 2001 | 3 | mobile | 2025-03-10 | 600 |
| 2002 | 1 | web | 2025-05-05 | 1000 |
| 2003 | 2 | web | 2025-05-10 | 900 |
| 2004 | 1 | api | 2025-05-15 | 300 |
| 2005 | 7 | web | 2025-05-18 | 500 |
| 2006 | 4 | web | 2025-05-20 | 300 |
| 2007 | 6 | mobile | 2025-05-28 | 800 |
| 2008 | 2 | mobile | 2025-05-30 | 1300 |
| 2009 | 3 | api | 2025-06-15 | 1500 |
| 2010 | 4 | web | 2025-08-30 | 200 |
| 2011 | 5 | api | 2025-09-01 | 50 |
Table advertiser_info
| column | type | constraints |
|---|---|---|
| advertiser_id | INT | PRIMARY KEY |
| advertiser_name | VARCHAR(100) | NOT NULL |
| advertiser_country | VARCHAR(50) | NOT NULL |
Sample data:
| advertiser_country | advertiser_id | advertiser_name |
|---|---|---|
| US | 1 | Acme |
| CA | 2 | Globex |
| US | 3 | Initech |
| UK | 4 | Umbrella |
| DE | 5 | Hooli |
| IN | 6 | Soylent |
| BR | 7 | MassiveDynamic |
| FR | 8 | Vehement |
| AU | 9 | WayneEnterprises |
| JP | 10 | StarkIndustries |
| MX | 11 | Wonka |
Expected output
One row per (date, creation_source) showing the total revenue on that date for that creation_source, ordered by date then creation_source.
| creation_source | daily_revenue | date |
|---|---|---|
| web | 500 | 2024-02-10 |
| web | 400 | 2024-04-15 |
| api | 300 | 2024-05-20 |
| api | 200 | 2024-06-10 |
| mobile | 100 | 2024-07-01 |
| mobile | 600 | 2024-07-10 |
| mobile | 1200 | 2024-07-20 |
| mobile | 600 | 2025-03-10 |
| web | 1000 | 2025-05-05 |
| web | 900 | 2025-05-10 |
| api | 300 | 2025-05-15 |
| web | 500 | 2025-05-18 |
| web | 300 | 2025-05-20 |
| mobile | 800 | 2025-05-28 |
| mobile | 1300 | 2025-05-30 |
| api | 1500 | 2025-06-15 |
| web | 200 | 2025-08-30 |
| api | 50 | 2025-09-01 |
Hints
- Group by both date and creation_source.
- 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.
Table active_ads
| column | type | constraints |
|---|---|---|
| date | DATE | NOT NULL |
| ad_id | INT | PRIMARY KEY |
| advertiser_id | INT | NOT NULL |
| creation_source | VARCHAR(20) | NOT NULL |
| revenue | DECIMAL(12,2) | NOT NULL |
Sample data:
| ad_id | advertiser_id | creation_source | date | revenue |
|---|---|---|---|---|
| 1001 | 1 | web | 2024-02-10 | 500 |
| 1002 | 4 | web | 2024-04-15 | 400 |
| 1003 | 1 | api | 2024-05-20 | 300 |
| 1004 | 5 | api | 2024-06-10 | 200 |
| 1005 | 2 | mobile | 2024-07-01 | 100 |
| 1006 | 3 | mobile | 2024-07-10 | 600 |
| 1007 | 8 | mobile | 2024-07-20 | 1200 |
| 2001 | 3 | mobile | 2025-03-10 | 600 |
| 2002 | 1 | web | 2025-05-05 | 1000 |
| 2003 | 2 | web | 2025-05-10 | 900 |
| 2004 | 1 | api | 2025-05-15 | 300 |
| 2005 | 7 | web | 2025-05-18 | 500 |
| 2006 | 4 | web | 2025-05-20 | 300 |
| 2007 | 6 | mobile | 2025-05-28 | 800 |
| 2008 | 2 | mobile | 2025-05-30 | 1300 |
| 2009 | 3 | api | 2025-06-15 | 1500 |
| 2010 | 4 | web | 2025-08-30 | 200 |
| 2011 | 5 | api | 2025-09-01 | 50 |
Table advertiser_info
| column | type | constraints |
|---|---|---|
| advertiser_id | INT | PRIMARY KEY |
| advertiser_name | VARCHAR(100) | NOT NULL |
| advertiser_country | VARCHAR(50) | NOT NULL |
Sample data:
| advertiser_country | advertiser_id | advertiser_name |
|---|---|---|
| US | 1 | Acme |
| CA | 2 | Globex |
| US | 3 | Initech |
| UK | 4 | Umbrella |
| DE | 5 | Hooli |
| IN | 6 | Soylent |
| BR | 7 | MassiveDynamic |
| FR | 8 | Vehement |
| AU | 9 | WayneEnterprises |
| JP | 10 | StarkIndustries |
| MX | 11 | Wonka |
Expected output
Ten countries with the smallest number of distinct active advertisers in the window 2025-05-03 to 2025-06-01 (inclusive), including countries with zero active advertisers. Sorted by count ascending, then country ascending.
| active_advertiser_count | advertiser_country |
|---|---|
| 0 | AU |
| 0 | DE |
| 0 | FR |
| 0 | JP |
| 0 | MX |
| 1 | BR |
| 1 | CA |
| 1 | IN |
| 1 | UK |
| 1 | US |
Hints
- First find the distinct active advertiser_ids in the date window using a CTE or subquery.
- 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.
Table active_ads
| column | type | constraints |
|---|---|---|
| date | DATE | NOT NULL |
| ad_id | INT | PRIMARY KEY |
| advertiser_id | INT | NOT NULL |
| creation_source | VARCHAR(20) | NOT NULL |
| revenue | DECIMAL(12,2) | NOT NULL |
Sample data:
| ad_id | advertiser_id | creation_source | date | revenue |
|---|---|---|---|---|
| 1001 | 1 | web | 2024-02-10 | 500 |
| 1002 | 4 | web | 2024-04-15 | 400 |
| 1003 | 1 | api | 2024-05-20 | 300 |
| 1004 | 5 | api | 2024-06-10 | 200 |
| 1005 | 2 | mobile | 2024-07-01 | 100 |
| 1006 | 3 | mobile | 2024-07-10 | 600 |
| 1007 | 8 | mobile | 2024-07-20 | 1200 |
| 2001 | 3 | mobile | 2025-03-10 | 600 |
| 2002 | 1 | web | 2025-05-05 | 1000 |
| 2003 | 2 | web | 2025-05-10 | 900 |
| 2004 | 1 | api | 2025-05-15 | 300 |
| 2005 | 7 | web | 2025-05-18 | 500 |
| 2006 | 4 | web | 2025-05-20 | 300 |
| 2007 | 6 | mobile | 2025-05-28 | 800 |
| 2008 | 2 | mobile | 2025-05-30 | 1300 |
| 2009 | 3 | api | 2025-06-15 | 1500 |
| 2010 | 4 | web | 2025-08-30 | 200 |
| 2011 | 5 | api | 2025-09-01 | 50 |
Table advertiser_info
| column | type | constraints |
|---|---|---|
| advertiser_id | INT | PRIMARY KEY |
| advertiser_name | VARCHAR(100) | NOT NULL |
| advertiser_country | VARCHAR(50) | NOT NULL |
Sample data:
| advertiser_country | advertiser_id | advertiser_name |
|---|---|---|
| US | 1 | Acme |
| CA | 2 | Globex |
| US | 3 | Initech |
| UK | 4 | Umbrella |
| DE | 5 | Hooli |
| IN | 6 | Soylent |
| BR | 7 | MassiveDynamic |
| FR | 8 | Vehement |
| AU | 9 | WayneEnterprises |
| JP | 10 | StarkIndustries |
| MX | 11 | Wonka |
Expected output
For each creation_source, the number of advertisers whose revenue increased by at least 1000 from 2024-01-01..2024-09-01 to 2025-01-01..2025-09-01, the total number of advertisers with positive spend in either period for that source, and the resulting proportion.
| creation_source | denom | num_grew_by_1000 | proportion |
|---|---|---|---|
| api | 3 | 1 | 0.3333 |
| mobile | 4 | 1 | 0.25 |
| web | 4 | 0 | 0 |
Hints
- First aggregate revenue per advertiser_id, creation_source, and period using conditional SUM with CASE on the date ranges.
- 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.