Retrieve Top Five Ads by Conversions in 30 Days
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Overview: This question evaluates a candidate's competency in SQL data manipulation, specifically aggregating event data and applying time-window filters to produce ranked metrics.
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).
Tables
ads(ad_id INTEGER, advertiser_id INTEGER, created_at DATE)
ad_impressions(impression_id INTEGER, ad_id INTEGER, user_id INTEGER, impression_date DATE)
ad_conversions(conversion_id INTEGER, impression_id INTEGER, revenue DECIMAL(10,2), conversion_date DATE)
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.