Top Earner Per Campaign

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

General Assembly

Problem

Our campaign recap names exactly one top earner per campaign, even when several users brought in the same top revenue. Rebuild it from ad_impressions with every top earner and their total revenue, leaving out impressions with no user attached, campaigns in alphabetical order and tied earners by user id.

The top-earning user per campaign.

Tables

ad_impressions

Columns: impression_id INTEGER, user_id INTEGER, ad_campaign TEXT, impression_time TEXT, clicked INTEGER, revenue REAL

Expected output

Worked solution and explanation

This is a top-with-ties problem dressed up as a campaign leaderboard. Everyone writes the GROUP BY ad_campaign, user_id and the SUM(revenue). What separates candidates is what they do when two users land on the same total. On this data the loyalty campaign has two users at exactly 22.5, so the right answer is 9 rows for 8 campaigns. Reach for a one-winner pick and you return 8, quietly crowning one of them. Nobody notices, because 8 rows for 8 campaigns looks exactly right.

Three moves, in this order

Step 1: Total each user inside each campaign

Drop rows where user_id is NULL first. Those impressions belong to nobody, and if you leave them in they form a phantom user who competes for the top. Then GROUP BY ad_campaign, user_id with SUM(revenue). The grain is the whole game: rank impressions instead of totals and the winner is whoever had the single biggest click, not the biggest earner.

Step 2: Rank the totals per campaign, keeping ties

RANK() OVER (PARTITION BY ad_campaign ORDER BY SUM(revenue) DESC) is evaluated after the GROUP BY, so it can sort by the aggregate directly. RANK gives equal totals the same number, so both 22.5 users get 1. Without the PARTITION BY you get one company-wide winner instead of one per campaign.

Step 3: Keep rank 1, then sort the output

A window result cannot be filtered in the WHERE of the query that computes it, so filter rnk = 1 one level out. Then sort by ad_campaign and user_id, which puts tied users side by side in a predictable order.

SELECT ad_campaign, user_id, total_revenue
FROM (
    SELECT
        ad_campaign,
        user_id,
        SUM(revenue) AS total_revenue,
        RANK() OVER (PARTITION BY ad_campaign ORDER BY SUM(revenue) DESC) AS rnk
    FROM ad_impressions
    WHERE user_id IS NOT NULL
    GROUP BY ad_campaign, user_id
) ranked
WHERE rnk = 1
ORDER BY ad_campaign, user_id

One winner per campaign

ROW_NUMBER() filtered to 1, or a per-campaign ORDER BY ... LIMIT 1, always yields exactly one row per campaign. Here that is 8 rows, and which tied user survives depends on the order the engine happened to read.

Every tied winner

RANK() filtered to 1 keeps every user whose total equals the campaign maximum: 9 rows. DENSE_RANK() behaves identically at rank 1; the two only differ further down.

SELECT ad_campaign, user_id, total_revenue
FROM (
    SELECT
        ad_campaign,
        user_id,
        SUM(revenue) AS total_revenue,
        ROW_NUMBER() OVER (PARTITION BY ad_campaign ORDER BY SUM(revenue) DESC) AS rn
    FROM ad_impressions
    WHERE user_id IS NOT NULL
    GROUP BY ad_campaign, user_id
) ranked
WHERE rn = 1
ORDER BY ad_campaign, user_id

Common follow-up questions

• How would you return the top three earners per campaign? (Swaps `rnk = 1` for `rnk <= 3` and probes how `RANK` and `DENSE_RANK` differ at the cut.)
• Should unclicked impressions count toward a user's revenue at all? (They carry `NULL` revenue, so `SUM` already ignores them; the point is noticing it.)
• How would you add each top earner's click-through rate in that campaign? (Adds `AVG(clicked)` to the same grouped query and carries it through the rank filter.)