Coding questions · Meta
Compute Daily Revenue by Creation Source
This question evaluates proficiency in data manipulation and analytical SQL/Python skills, including aggregation, date truncation, joins, window functions, and period-over-period comparisons for revenue metrics.
active_ads
date | ad_id | advertiser_id | creation_source | revenue
2023-09-01 | 1001 | 17 | mobile_app | 150.00
2023-09-01 | 1002 | 18 | api | 75.00
2023-09-02 | 1003 | 17 | web | 200.00
advertiser_info
advertiser_id | advertiser_name | advertiser_country
17 | Alpha Corp | US
18 | Beta Ltd | UK
19 | Gamma GmbH | DE
Scenario
Advertising revenue reporting for a multi-source ads platform using relational data in tables active_ads and advertiser_info.
Question
Q1. Write a query to compute daily total revenue grouped by creation_source. Q2. Write a query to list the countries of the ten advertisers with the fewest active ads (break ties arbitrarily). Q3. For each creation_source, compute the proportion of advertisers whose spend in the current year exceeds their spend in the previous year by at least 1,000 USD.
Hints
Use date truncation, aggregation, LEFT JOINs, window functions or CTEs for yearly spend, then COUNTIF / COUNT ratio.
Daily Total Revenue by Creation Source
You are given an ads revenue table active_ads. Write a SQL query to compute the daily total revenue grouped by creation_source.
Return one row per (ad_date, creation_source) with the summed revenue for that date and source.
Table active_ads
| column | type | constraints |
|---|---|---|
| ad_date | DATE | NOT NULL |
| ad_id | INT | PRIMARY KEY |
| advertiser_id | INT | NOT NULL |
| creation_source | VARCHAR(20) | NOT NULL |
| revenue | DECIMAL(10,2) | NOT NULL |
Sample data:
| ad_date | ad_id | advertiser_id | creation_source | revenue |
|---|---|---|---|---|
| 2023-09-01 | 1001 | 17 | mobile_app | 150 |
| 2023-09-01 | 1002 | 18 | api | 75 |
| 2023-09-02 | 1003 | 17 | web | 200 |
| 2023-09-02 | 1004 | 19 | mobile_app | 50 |
| 2023-09-03 | 1005 | 20 | api | 120 |
| 2023-09-03 | 1006 | 18 | web | 80 |
| 2023-09-03 | 1007 | 21 | mobile_app | 60 |
| 2023-09-04 | 1008 | 22 | api | 40 |
| 2023-09-04 | 1009 | 22 | web | 30 |
| 2023-09-05 | 1010 | 23 | web | 20 |
| 2023-09-05 | 1011 | 23 | web | 25 |
| 2023-09-06 | 1012 | 23 | api | 10 |
| 2023-09-06 | 1013 | 24 | mobile_app | 70 |
| 2024-02-10 | 2001 | 17 | api | 300 |
| 2024-05-15 | 2002 | 17 | api | 200 |
| 2024-03-01 | 2003 | 18 | api | 1000 |
| 2024-06-20 | 2004 | 17 | web | 200 |
| 2024-04-05 | 2005 | 18 | web | 700 |
| 2024-07-07 | 2006 | 19 | mobile_app | 600 |
| 2024-08-08 | 2007 | 20 | mobile_app | 100 |
| 2025-01-10 | 3001 | 17 | api | 1000 |
| 2025-02-11 | 3002 | 17 | api | 800 |
| 2025-03-12 | 3003 | 18 | api | 1500 |
| 2025-04-13 | 3004 | 22 | api | 900 |
| 2025-01-05 | 3005 | 17 | web | 1500 |
| 2025-02-06 | 3006 | 18 | web | 400 |
| 2025-05-07 | 3007 | 23 | web | 1200 |
| 2025-03-03 | 3008 | 19 | mobile_app | 1300 |
| 2025-04-04 | 3009 | 20 | mobile_app | 1200 |
| 2025-05-05 | 3010 | 21 | mobile_app | 500 |
Expected output
Each row contains a date, a creation_source, and the summed revenue for that date/source.
| ad_date | creation_source | total_revenue |
|---|---|---|
| 2023-09-01 | api | 75 |
| 2023-09-01 | mobile_app | 150 |
| 2023-09-02 | mobile_app | 50 |
| 2023-09-02 | web | 200 |
| 2023-09-03 | api | 120 |
| 2023-09-03 | mobile_app | 60 |
| 2023-09-03 | web | 80 |
| 2023-09-04 | api | 40 |
| 2023-09-04 | web | 30 |
| 2023-09-05 | web | 45 |
| 2023-09-06 | api | 10 |
| 2023-09-06 | mobile_app | 70 |
| 2024-02-10 | api | 300 |
| 2024-03-01 | api | 1000 |
| 2024-04-05 | web | 700 |
| 2024-05-15 | api | 200 |
| 2024-06-20 | web | 200 |
| 2024-07-07 | mobile_app | 600 |
| 2024-08-08 | mobile_app | 100 |
| 2025-01-05 | web | 1500 |
| 2025-01-10 | api | 1000 |
| 2025-02-06 | web | 400 |
| 2025-02-11 | api | 800 |
| 2025-03-03 | mobile_app | 1300 |
| 2025-03-12 | api | 1500 |
| 2025-04-04 | mobile_app | 1200 |
| 2025-04-13 | api | 900 |
| 2025-05-05 | mobile_app | 500 |
| 2025-05-07 | web | 1200 |
Hints
- Group by both the date and creation_source.
- Use SUM(revenue) for totals.
Countries of the 10 Advertisers with the Fewest Active Ads
You are given tables active_ads and advertiser_info.
Write a SQL query to list the countries of the ten advertisers with the fewest active ads. Count active ads as the number of distinct ad_id values in active_ads per advertiser. Include advertisers with zero active ads. Break ties arbitrarily.
Return (advertiser_id, advertiser_country, active_ad_count) for the 10 advertisers with the smallest counts.
Table advertiser_info
| column | type | constraints |
|---|---|---|
| advertiser_id | INT | PRIMARY KEY |
| advertiser_name | VARCHAR(100) | NOT NULL |
| advertiser_country | VARCHAR(2) | NOT NULL |
Sample data:
| advertiser_country | advertiser_id | advertiser_name |
|---|---|---|
| US | 17 | Alpha Corp |
| UK | 18 | Beta Ltd |
| DE | 19 | Gamma GmbH |
| FR | 20 | Delta SAS |
| JP | 21 | Epsilon KK |
| AU | 22 | Zeta Pty |
| IT | 23 | Eta SpA |
| NL | 24 | Theta BV |
| SE | 25 | Iota AB |
| ES | 26 | Kappa SL |
| BR | 27 | Lambda SA |
| CA | 28 | Mu Co |
Table active_ads
| column | type | constraints |
|---|---|---|
| ad_date | DATE | NOT NULL |
| ad_id | INT | PRIMARY KEY |
| advertiser_id | INT | NOT NULL |
| creation_source | VARCHAR(20) | NOT NULL |
| revenue | DECIMAL(10,2) | NOT NULL |
Sample data:
| ad_date | ad_id | advertiser_id | creation_source | revenue |
|---|---|---|---|---|
| 2023-09-01 | 1001 | 17 | mobile_app | 150 |
| 2023-09-01 | 1002 | 18 | api | 75 |
| 2023-09-02 | 1003 | 17 | web | 200 |
| 2023-09-02 | 1004 | 19 | mobile_app | 50 |
| 2023-09-03 | 1005 | 20 | api | 120 |
| 2023-09-03 | 1006 | 18 | web | 80 |
| 2023-09-03 | 1007 | 21 | mobile_app | 60 |
| 2023-09-04 | 1008 | 22 | api | 40 |
| 2023-09-04 | 1009 | 22 | web | 30 |
| 2023-09-05 | 1010 | 23 | web | 20 |
| 2023-09-05 | 1011 | 23 | web | 25 |
| 2023-09-06 | 1012 | 23 | api | 10 |
| 2023-09-06 | 1013 | 24 | mobile_app | 70 |
| 2024-02-10 | 2001 | 17 | api | 300 |
| 2024-05-15 | 2002 | 17 | api | 200 |
| 2024-03-01 | 2003 | 18 | api | 1000 |
| 2024-06-20 | 2004 | 17 | web | 200 |
| 2024-04-05 | 2005 | 18 | web | 700 |
| 2024-07-07 | 2006 | 19 | mobile_app | 600 |
| 2024-08-08 | 2007 | 20 | mobile_app | 100 |
| 2025-01-10 | 3001 | 17 | api | 1000 |
| 2025-02-11 | 3002 | 17 | api | 800 |
| 2025-03-12 | 3003 | 18 | api | 1500 |
| 2025-04-13 | 3004 | 22 | api | 900 |
| 2025-01-05 | 3005 | 17 | web | 1500 |
| 2025-02-06 | 3006 | 18 | web | 400 |
| 2025-05-07 | 3007 | 23 | web | 1200 |
| 2025-03-03 | 3008 | 19 | mobile_app | 1300 |
| 2025-04-04 | 3009 | 20 | mobile_app | 1200 |
| 2025-05-05 | 3010 | 21 | mobile_app | 500 |
Expected output
10 rows: the advertisers with the smallest distinct ad_id counts (including zero), showing their country and count.
| active_ad_count | advertiser_country | advertiser_id |
|---|---|---|
| 0 | SE | 25 |
| 0 | ES | 26 |
| 0 | BR | 27 |
| 0 | CA | 28 |
| 1 | NL | 24 |
| 2 | JP | 21 |
| 3 | DE | 19 |
| 3 | FR | 20 |
| 3 | AU | 22 |
| 4 | IT | 23 |
Hints
- Use a LEFT JOIN to keep advertisers with 0 ads.
- COUNT(DISTINCT ad_id) counts ads per advertiser.
YoY Advertiser Growth Proportion by Creation Source (2025 vs 2024)
Assume the current date is 2025-06-01. Using active_ads, for each creation_source, compute the proportion of advertisers whose spend (sum of revenue) in 2025 exceeds their spend in 2024 by at least 1,000 USD.
Definitions:
- 2025 spend = sum(revenue) for rows with ad_date between 2025-01-01 and 2025-12-31.
- 2024 spend = sum(revenue) for rows with ad_date between 2024-01-01 and 2024-12-31.
- Consider an advertiser part of a creation_source group if they have any spend in 2024 or 2025 for that creation_source.
- Treat missing spend in a year as 0.
Return one row per creation_source with:
- total_advertisers
- advertisers_meeting_threshold
- proportion_meeting_threshold (advertisers_meeting_threshold / total_advertisers as a decimal).
Table active_ads
| column | type | constraints |
|---|---|---|
| ad_date | DATE | NOT NULL |
| ad_id | INT | PRIMARY KEY |
| advertiser_id | INT | NOT NULL |
| creation_source | VARCHAR(20) | NOT NULL |
| revenue | DECIMAL(10,2) | NOT NULL |
Sample data:
| ad_date | ad_id | advertiser_id | creation_source | revenue |
|---|---|---|---|---|
| 2023-09-01 | 1001 | 17 | mobile_app | 150 |
| 2023-09-01 | 1002 | 18 | api | 75 |
| 2023-09-02 | 1003 | 17 | web | 200 |
| 2023-09-02 | 1004 | 19 | mobile_app | 50 |
| 2023-09-03 | 1005 | 20 | api | 120 |
| 2023-09-03 | 1006 | 18 | web | 80 |
| 2023-09-03 | 1007 | 21 | mobile_app | 60 |
| 2023-09-04 | 1008 | 22 | api | 40 |
| 2023-09-04 | 1009 | 22 | web | 30 |
| 2023-09-05 | 1010 | 23 | web | 20 |
| 2023-09-05 | 1011 | 23 | web | 25 |
| 2023-09-06 | 1012 | 23 | api | 10 |
| 2023-09-06 | 1013 | 24 | mobile_app | 70 |
| 2024-02-10 | 2001 | 17 | api | 300 |
| 2024-05-15 | 2002 | 17 | api | 200 |
| 2024-03-01 | 2003 | 18 | api | 1000 |
| 2024-06-20 | 2004 | 17 | web | 200 |
| 2024-04-05 | 2005 | 18 | web | 700 |
| 2024-07-07 | 2006 | 19 | mobile_app | 600 |
| 2024-08-08 | 2007 | 20 | mobile_app | 100 |
| 2025-01-10 | 3001 | 17 | api | 1000 |
| 2025-02-11 | 3002 | 17 | api | 800 |
| 2025-03-12 | 3003 | 18 | api | 1500 |
| 2025-04-13 | 3004 | 22 | api | 900 |
| 2025-01-05 | 3005 | 17 | web | 1500 |
| 2025-02-06 | 3006 | 18 | web | 400 |
| 2025-05-07 | 3007 | 23 | web | 1200 |
| 2025-03-03 | 3008 | 19 | mobile_app | 1300 |
| 2025-04-04 | 3009 | 20 | mobile_app | 1200 |
| 2025-05-05 | 3010 | 21 | mobile_app | 500 |
Expected output
One row per creation_source showing how many advertisers exist for that source (in 2024 or 2025), how many increased by at least 1000, and the resulting proportion.
| advertisers_meeting_threshold | creation_source | proportion_meeting_threshold | total_advertisers |
|---|---|---|---|
| 1 | api | 0.3333 | 3 |
| 1 | mobile_app | 0.3333 | 3 |
| 2 | web | 0.6667 | 3 |
Hints
- Compute yearly spend per (creation_source, advertiser_id) using conditional aggregation.
- Treat missing year spend as 0 by using ELSE 0 in SUM(CASE...).
- Proportion = qualifying_count / total_count; use CAST to avoid integer division.