Rank Ads by Conversion Rate for Top 10 Performers
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
ad
id | advertiser_id | created_at
1 | 101 | 2023-07-01
2 | 102 | 2023-07-05
3 | 101 | 2023-07-10
impression
id | ad_id | user_id | timestamp
10 | 1 | 555 | 2023-07-11 10:00
11 | 1 | 556 | 2023-07-11 10:05
12 | 2 | 557 | 2023-07-12 09:00
conversion
id | impression_id | revenue | timestamp
100 | 10 | 5.00 | 2023-07-11 10:10
101 | 12 | 10.00 | 2023-07-12 09:15
##### Scenario
Advertising platform wants to track ad effectiveness over the last 30 days.
##### Question
Given tables ad, impression and conversion, write SQL to return for every ad_id: total impressions, total conversions, conversion_rate and total_revenue for the past 30 days. Extend the query to rank ads by conversion_rate and return the top 10 performers.
##### Hints
Think joins (impression → conversion), date filters, group-by ad_id, and safe division for rates.
Overview: This question evaluates data manipulation and analytics skills for computing time-windowed ad performance metrics, including aggregations of impressions and revenue, join logic between impression and conversion records, conversion-rate calculation, and ranking.
An advertising platform wants to track ad effectiveness between 2025-05-03 and 2025-06-01 (inclusive), representing the most recent 30-day period relative to 2025-06-01.
Given the tables ad, impression, and conversion, write a SQL query that returns, for every ad_id:
- total_impressions
- total_conversions
- conversion_rate (total_conversions / total_impressions, treating 0 impressions as a 0 rate)
- total_revenue
Only impressions and conversions whose timestamps fall between 2025-05-03 and 2025-06-01 (inclusive) should be counted. Extend the query to rank ads by conversion_rate and return the top 10 performers. If multiple ads have the same conversion_rate, break ties by higher total_revenue, then higher total_impressions, then lower ad_id.
Tables
ad(id INTEGER, advertiser_id INTEGER, created_at DATE)
impression(id INTEGER, ad_id INTEGER, user_id INTEGER, timestamp TIMESTAMP)
conversion(id INTEGER, impression_id INTEGER, revenue DECIMAL(10,2), timestamp TIMESTAMP)
Hints
- Join impressions to conversions via impression_id to count conversions per ad.
- Filter impression.timestamp and conversion.timestamp to be between '2025-05-03' and '2025-06-01'.