Top Two Ads by Conversions in Each Campaign

Medium
283.

Top Two Ads by Conversions in Each Campaign

MediumWindow Functions

MySQL

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

ColumnType
ad_idint
campaign_idint
headlinevarchar
conversionsint

Primary key: ad_id.

One row per ad creative with its conversions to date.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Ad

ad_idcampaign_idheadlineconversions
110Lights, Deals, Diwali42
210Up to 60% Off Today42
310Gift Hampers from Rs 49930
420Rain-Ready Sandals18
520Monsoon Sale Is Live25
620Free Delivery on Shoes11
730Back to College Kits0
830Laptop Bags Under Rs 9997

Output

campaign_idad_idheadlineconversions
101Lights, Deals, Diwali42
102Up to 60% Off Today42
205Monsoon Sale Is Live25
204Rain-Ready Sandals18
308Laptop Bags Under Rs 9997
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
ad_idint
campaign_idint
headlinevarchar
conversionsint

Primary key: ad_id.

One row per ad creative with its conversions to date.

Examples

Example 1

Ad

ad_idcampaign_idheadlineconversions
110Lights, Deals, Diwali42
210Up to 60% Off Today42
310Gift Hampers from Rs 49930
420Rain-Ready Sandals18
520Monsoon Sale Is Live25
620Free Delivery on Shoes11
730Back to College Kits0
830Laptop Bags Under Rs 9997

Output

campaign_idad_idheadlineconversions
101Lights, Deals, Diwali42
102Up to 60% Off Today42
205Monsoon Sale Is Live25
204Rain-Ready Sandals18
308Laptop Bags Under Rs 9997

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 <= 2

Another 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) < 2

Another 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 →