Coding questions · Meta
Retrieve Top Five Ads by Conversions in 30 Days
This question evaluates a candidate's competency in SQL data manipulation, specifically aggregating event data and applying time-window filters to produce ranked metrics.
ads
ad_id | advertiser_id | created_at
1 | 101 | 2024-06-01
2 | 102 | 2024-06-03
3 | 101 | 2024-06-10
ad_impressions
impression_id | ad_id | user_id | impression_date
1001 | 1 | 501 | 2024-06-15
1002 | 2 | 502 | 2024-06-15
1003 | 1 | 503 | 2024-06-16
1004 | 3 | 501 | 2024-06-17
1005 | 2 | 504 | 2024-06-18
ad_conversions
conversion_id | impression_id | revenue | conversion_date
2001 | 1001 | 5.00 | 2024-06-15
2002 | 1003 | 2.50 | 2024-06-16
2003 | 1005 | 7.00 | 2024-06-18
Scenario
Analyze advertising performance; return the top N ads by conversions in the last M days using three related tables.
Question
Write SQL to return the five ads with the highest number of conversions in the past 30 days. Output ad_id, advertiser_id, total_conversions, sorted by total_conversions DESC. Exclude ads with zero conversions and handle impressions that never converted.
Hints
LEFT JOIN impressions to conversions, filter on date range, GROUP BY ad_id.
Top 5 Ads by Conversions in the Last 30 Days
As of 2025-06-01, write an SQL query to return the five ads with the highest number of conversions in the last 30 days (i.e., between 2025-05-03 and 2025-06-01, inclusive). Use the ads, ad_impressions, and ad_conversions tables. Output columns: ad_id, advertiser_id, total_conversions. Count only conversions whose conversion_date falls within this date range. Exclude ads with zero conversions in this period and correctly handle impressions that never converted. Order the result by total_conversions in descending order (and by ad_id ascending to break ties).
Table ads
| column | type | constraints |
|---|---|---|
| ad_id | INTEGER | PRIMARY KEY, NOT NULL |
| advertiser_id | INTEGER | NOT NULL |
| created_at | DATE | NOT NULL |
Sample data:
| ad_id | advertiser_id | created_at |
|---|---|---|
| 1 | 101 | 2025-05-01 |
| 2 | 102 | 2025-05-03 |
| 3 | 101 | 2025-05-10 |
Table ad_impressions
| column | type | constraints |
|---|---|---|
| impression_id | INTEGER | PRIMARY KEY, NOT NULL |
| ad_id | INTEGER | NOT NULL, FOREIGN KEY REFERENCES ads(ad_id) |
| user_id | INTEGER | NOT NULL |
| impression_date | DATE | NOT NULL |
Sample data:
| ad_id | impression_date | impression_id | user_id |
|---|---|---|---|
| 1 | 2025-05-15 | 1001 | 501 |
| 2 | 2025-05-15 | 1002 | 502 |
| 1 | 2025-05-16 | 1003 | 503 |
| 3 | 2025-05-17 | 1004 | 501 |
| 2 | 2025-05-18 | 1005 | 504 |
Table ad_conversions
| column | type | constraints |
|---|---|---|
| conversion_id | INTEGER | PRIMARY KEY, NOT NULL |
| impression_id | INTEGER | NOT NULL, FOREIGN KEY REFERENCES ad_impressions(impression_id) |
| revenue | DECIMAL(10,2) | NOT NULL |
| conversion_date | DATE | NOT NULL |
Sample data:
| conversion_date | conversion_id | impression_id | revenue |
|---|---|---|---|
| 2025-05-15 | 2001 | 1001 | 5 |
| 2025-05-16 | 2002 | 1003 | 2.5 |
| 2025-05-18 | 2003 | 1005 | 7 |
Expected output
Return up to five ads with the most conversions between 2025-05-03 and 2025-06-01 (inclusive), as of 2025-06-01. Count only conversions whose conversion_date falls within this window, and exclude ads with zero such conversions. Based on the provided sample data, all three conversions are within the window. Ad 1 has two conversions (impressions 1001 and 1003), ad 2 has one conversion (impression 1005), and ad 3 has none. Therefore, the result is:
| ad_id | advertiser_id | total_conversions |
|---|---|---|
| 1 | 101 | 2 |
| 2 | 102 | 1 |
Hints
- Join ads to ad_impressions on ad_id, then left join to ad_conversions on impression_id.
- Apply the 2025-05-03 to 2025-06-01 date filter on conversion_date.
- Group by ad_id and advertiser_id, count conversions, filter out groups with zero conversions, and limit to 5.