Top Two Ads by Conversions in Each Campaign — SQL Problem
Each campaign rotates several ad creatives. Before the next refresh, the creative team keeps the best performers of every campaign and retires the rest.
- Difficulty: Medium
- Topics: Window Functions
- Dialect: MySQL
- Problem: #283
Problem statement
Each campaign rotates several ad creatives. Before the next refresh, the creative team keeps the best performers of every campaign and retires the rest.
Rank the ads of each campaign by conversions, highest first, with standard competition ranking — tied ads share a place and the next place is skipped (two ads tied for first leave no second place). Return campaign_id, ad_id, headline and conversions for the ads ranked 1 or 2. An ad with 0 conversions is never kept. Return the rows in any order.
Tables
Table: Ad
| Column | Type |
|---|---|
| ad_id | int |
| campaign_id | int |
| headline | varchar |
| conversions | int |
Primary key: ad_id.
One row per ad creative with its conversions to date.
Examples
Example 1
Ad
| ad_id | campaign_id | headline | conversions |
|---|---|---|---|
| 1 | 10 | Lights, Deals, Diwali | 42 |
| 2 | 10 | Up to 60% Off Today | 42 |
| 3 | 10 | Gift Hampers from Rs 499 | 30 |
| 4 | 20 | Rain-Ready Sandals | 18 |
| 5 | 20 | Monsoon Sale Is Live | 25 |
| 6 | 20 | Free Delivery on Shoes | 11 |
| 7 | 30 | Back to College Kits | 0 |
| 8 | 30 | Laptop Bags Under Rs 999 | 7 |
Output
| campaign_id | ad_id | headline | conversions |
|---|---|---|---|
| 10 | 1 | Lights, Deals, Diwali | 42 |
| 10 | 2 | Up to 60% Off Today | 42 |
| 20 | 5 | Monsoon Sale Is Live | 25 |
| 20 | 4 | Rain-Ready Sandals | 18 |
| 30 | 8 | Laptop Bags Under Rs 999 | 7 |
How to solve Top Two Ads by Conversions in Each Campaign
Standard competition ranking is RANK(): ties share a place and the places after a tie are skipped, so 42, 42, 30 rank 1, 1, 3 and only the two tied ads survive. DENSE_RANK() would give 1, 1, 2 and keep the third ad, ROW_NUMBER() would keep exactly two and pick between ties arbitrarily — both answer a different question.
Rank inside a derived table, since a window result cannot be filtered in the WHERE of the same query, and filter conversions > 0 before ranking so an ad that never converted cannot take a place in a campaign with a single good ad.
The rank also has a definition without windows: 1 + the number of ads in the same campaign with strictly more conversions. "Rank ≤ 2" is then "fewer than two better ads", which the correlated COUNT and the self-join HAVING COUNT(b.ad_id) < 2 express. Those are quadratic within a campaign; the window is one sort.
Reference solution (MySQL)
SELECT campaign_id, ad_id, headline, conversions
FROM (
SELECT campaign_id, ad_id, headline, conversions,
RANK() OVER (PARTITION BY campaign_id ORDER BY conversions DESC) AS rnk
FROM Ad
WHERE conversions > 0
) r
WHERE rnk <= 2Another way
SELECT campaign_id, ad_id, headline, conversions FROM Ad a WHERE conversions > 0 AND (SELECT COUNT(*) FROM Ad b WHERE b.campaign_id = a.campaign_id AND b.conversions > a.conversions) < 2Another way
SELECT a.campaign_id, a.ad_id, a.headline, a.conversions
FROM Ad a LEFT JOIN Ad b ON b.campaign_id = a.campaign_id AND b.conversions > a.conversions
WHERE a.conversions > 0
GROUP BY a.ad_id, a.campaign_id, a.headline, a.conversions
HAVING COUNT(b.ad_id) < 2← Cost per Acquisition of Every Campaign · Campaign Months That Overspent the Budget →