# Top Earner Per Campaign

> The top-earning user per campaign.

Canonical URL: <https://datadriven.io/problems/top_earner_per_campaign>

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

For each campaign, show the top-earning user and their total revenue. If there's a tie, show all tied users.

## Worked solution and explanation

### Why this problem exists in real interviews

This uses `ad_impressions` to probe per-group ranking via `ROW_NUMBER()` or `DENSE_RANK()` partitioned by a grouping key. It reveals whether a candidate understands the difference between `ROW_NUMBER`, `RANK`, and `DENSE_RANK` and picks the right one for the output contract.

---

### Break down the requirements

#### Step 1: Aggregate per user_id

`GROUP BY user_id` with the appropriate aggregate function produces one summary row per group from the `ad_impressions` table.

#### Step 2: Rank within each ad_campaign

Use `ROW_NUMBER() OVER (PARTITION BY ad_campaign ORDER BY aggregate DESC)` to rank entries within each partition.

#### Step 3: Filter to top entries

Wrap in a subquery and filter `WHERE rn <= N` to keep only the top entries per group.

---

### The solution

**Sum revenue per user-campaign pair then dense-rank within each campaign**

```sql
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
    GROUP BY ad_campaign, user_id
) ranked
WHERE rn <= 10
ORDER BY ad_campaign, total_revenue DESC
```

> **Cost Analysis**
>
> The GROUP BY reduces the 350M-row `ad_impressions` table to the number of distinct `user_id` values. The window function sorts within each partition. A covering index on `(user_id, revenue)` enables an index-only aggregate scan.

> **Interviewers Watch For**
>
> Interviewers specifically test whether you use `PARTITION BY` in the window function. Omitting it gives a global ranking instead of per-group, which is at its core different.

> **Common Pitfall**
>
> Using `ORDER BY ... LIMIT` instead of a window function for per-group ranking. LIMIT gives N rows globally, not per group. Per-group top-N always requires a window function.

---

## Common follow-up questions

- If a campaign has only one user, does DENSE_RANK still assign rank 1 correctly? _(Tests edge case; a single-row partition correctly gets rank 1.)_
- Should non-clicked impressions contribute to a user's revenue total within a campaign? _(Tests prompt interpretation; the prompt does not restrict to clicked, so all revenue rows count.)_
- How would you extend this to also show each top earner's click-through rate for that campaign? _(Tests adding a derived metric (COUNT clicked / COUNT all) to the grouped query.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/top_earner_per_campaign)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.