Interview Question Bank · Filter before ranking · ROW_NUMBER · tie-breakers
5. Best ad per campaign, with a minimum of clicks
MediumMarketingQuestion #173
Concept: Filter before ranking · ROW_NUMBER · tie-breakersshow
To pick one winner per group, number the rows with ROW_NUMBER() OVER (PARTITION BY group ORDER BY metric DESC, tie-breakers) and keep row 1. Small samples produce extreme rates, so apply a minimum-volume rule in WHERE before ranking. Spell out tie-breakers in the window's ORDER BY, or the database picks between tied rows arbitrarily.
Example (different data):
WITH r AS (
SELECT store, product, units, returns,
ROW_NUMBER() OVER (PARTITION BY store ORDER BY 1.0 * returns / units, units DESC, product) AS rn
FROM product_returns
WHERE units >= 50
)
SELECT store, product FROM r WHERE rn = 1;Your task
Larkspur Roasters wants to pause the weaker creatives in each campaign and keep only the best converter running. Ads with under 100 clicks haven't had enough traffic to judge.
Among ads with at least 100 clicks, find each campaign's ad with the highest conversion rate (conversions / clicks); on a tie, the ad with more clicks wins, then ad_name. Return campaign (campaign name), ad_name, clicks, conversions and cvr_pct (100 * conversions / clicks, rounded to 2 decimals). Campaigns with no qualifying ad are left out. Sort by campaign.
Starting Postgres in your browser…
Checking your access…