Coding questions · Meta
Rank Ads by Conversion Rate for Top 10 Performers
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.
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.
Top 10 Ads by Conversion Rate in a 30-Day Window
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.
Table ad
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| advertiser_id | INTEGER | NOT NULL |
| created_at | DATE | NOT NULL |
Sample data:
| advertiser_id | created_at | id |
|---|---|---|
| 101 | 2025-05-01 | 1 |
| 102 | 2025-05-05 | 2 |
| 101 | 2025-05-10 | 3 |
Table impression
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| ad_id | INTEGER | NOT NULL, FOREIGN KEY -> ad.id |
| user_id | INTEGER | NOT NULL |
| timestamp | TIMESTAMP | NOT NULL |
Sample data:
| ad_id | id | timestamp | user_id |
|---|---|---|---|
| 1 | 10 | 2025-05-11 10:00:00 | 555 |
| 1 | 11 | 2025-05-11 10:05:00 | 556 |
| 2 | 12 | 2025-05-12 09:00:00 | 557 |
Table conversion
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| impression_id | INTEGER | NOT NULL, FOREIGN KEY -> impression.id |
| revenue | DECIMAL(10,2) | NOT NULL |
| timestamp | TIMESTAMP | NOT NULL |
Sample data:
| id | impression_id | revenue | timestamp |
|---|---|---|---|
| 100 | 10 | 5.00 | 2025-05-11 10:10:00 |
| 101 | 12 | 10.00 | 2025-05-12 09:15:00 |
Expected output
One row per ad_id for impressions and conversions occurring between 2025-05-03 and 2025-06-01 (inclusive), with total_impressions, total_conversions, conversion_rate, total_revenue, and a rank. Ranking is by conversion_rate (descending), then total_revenue (descending), then total_impressions (descending), then ad_id (ascending). With the provided sample data (all within this 30-day window), the results are:
| ad_id | conversion_rate | rank | total_conversions | total_impressions | total_revenue |
|---|---|---|---|---|---|
| 2 | 1 | 1 | 1 | 1 | 10.00 |
| 1 | 0.5 | 2 | 1 | 2 | 5.00 |
| 3 | 0 | 3 | 0 | 0 | 0.00 |
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'.
- Group by ad_id to aggregate counts of impressions, conversions, and total revenue.
- Use COUNT(DISTINCT ...) where needed to avoid overcounting due to joins.
- Use safe division (e.g., total_conversions / NULLIF(total_impressions, 0)) so ads with zero impressions get a conversion_rate of 0 instead of causing an error.
- Use a window function such as RANK() ordered by conversion_rate (and tie-breakers) and then LIMIT 10 to get the top performers.