# Top Ad Campaigns by Revenue

> Every campaign has a bottom line. Stack them up.

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

Domain: SQL · Difficulty: easy · Seniority: L3 · Asked in: Audible

## Problem

The ad ops team is building the quarterly revenue leaderboard and only has room for the top five campaigns. Give them those five by total ad revenue, biggest first.

## Tables

### ad_impressions

- impression_id INTEGER
- user_id INTEGER
- ad_campaign TEXT
- impression_time TEXT
- clicked INTEGER
- revenue REAL

Sample rows (10 of 200):

| impression_id | user_id | ad_campaign | impression_time | clicked | revenue |
| --- | --- | --- | --- | --- | --- |
| 253 | 100 | BRAND_AWARENESS_Q1 | 2026-02-02 01:07:00 | 0 | NULL |
| 306 | 197 | PRODUCT_LAUNCH_X | 2026-03-03 02:14:00 | 0 | NULL |
| 359 | 294 | RETARGETING_CART | 2026-04-04 03:21:00 | 0 | NULL |
| 412 | 391 | HOLIDAY_PROMO | 2026-05-05 04:28:00 | 0 | NULL |
| 465 | 488 | NEW_USER_ACQU | 2026-06-06 05:35:00 | 1 | 0.2 |
| 518 | NULL | LOYALTY_PROGRAM | 2026-07-07 06:42:00 | 0 | NULL |
| 571 | 682 | FLASH_SALE_48H | 2026-08-08 07:49:00 | 0 | NULL |
| 624 | 779 | SUMMER_SALE_2024 | 2026-09-09 08:56:00 | 0 | NULL |
| 677 | 876 | BRAND_AWARENESS_Q1 | 2026-01-10 09:03:00 | 0 | NULL |
| 730 | 973 | PRODUCT_LAUNCH_X | 2026-02-11 10:10:00 | 1 | 0.35 |

## Expected output

First 5 of 5 rows:

| ad_campaign | total_revenue |
| --- | --- |
| LOYALTY_PROGRAM | 215.35 |
| SUMMER_SALE_2024 | 16.7 |
| HOLIDAY_PROMO | 6.2 |
| FLASH_SALE_48H | 5.6 |
| PRODUCT_LAUNCH_X | 5 |

## Worked solution and explanation

### What this really is

Under the quarterly-report costume this is a grouped total finished with a top-N cut. Summing revenue per campaign is the easy half, and anyone can write that part. What actually gets graded is whether you finish the job: order the campaigns by their total and keep only the five the leaderboard has room for. Leave the cut off and you hand back all eight campaigns, three of which never belonged on the board. Sort raw impression rows instead of aggregating first and every number you return is a single impression, not a campaign total.

---

### Break down the requirements

#### Step 1: Total revenue per campaign

`GROUP BY ad_campaign` with `SUM(revenue)` collapses the many impression rows per campaign into one total. SUM skips the NULL revenue values on its own, so unpaid impressions neither zero out a campaign nor drop it.

#### Step 2: Rank, then keep the leaders

`ORDER BY total_revenue DESC` puts the biggest earners on top, then `LIMIT 5` keeps only the leaders. The order is what makes the cut meaningful: without it, LIMIT would return five arbitrary campaigns.

---

### The solution

**Sum revenue per campaign, sort descending, keep the top five**

```sql
SELECT
    ad_campaign,
    SUM(revenue) AS total_revenue
FROM ad_impressions
GROUP BY ad_campaign
ORDER BY total_revenue DESC
LIMIT 5
```

> **Cost Analysis**
>
> The GROUP BY reduces the 200M-row `ad_impressions` table to one row per distinct `ad_campaign` before the sort ever runs, so the ORDER BY and LIMIT touch only a handful of aggregated rows. A covering index on `(ad_campaign, revenue)` enables an index-only aggregate scan.

> **Interviewers Watch For**
>
> The tell is whether you cap the output. A GROUP BY with an ORDER BY that returns every campaign is a candidate who read half the ask; the report is a top-five leaderboard, and the LIMIT is the line that makes it one.

> **Common Pitfall**
>
> Dropping the LIMIT and returning all eight campaigns. On a wider seed with a hundred campaigns this floods the report; the cut is load-bearing, not decoration. Equally wrong: sorting before aggregating, which ranks individual impressions instead of campaign totals.

---

## Common follow-up questions

- If some impressions have NULL revenue (organic placements), how does SUM treat those rows? _(Tests knowledge that SUM skips NULLs, which may undercount if NULLs should map to zero.)_
- If two campaigns tie on total revenue right at the fifth spot, how do you make the cut deterministic? _(Tests whether a total-ordering tiebreaker is needed so the fifth and sixth campaigns cannot swap non-deterministically.)_
- How would you add a column showing each campaign's percentage of total revenue? _(Tests ability to use a window SUM or scalar subquery for the denominator alongside the grouped query.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/top_ad_campaigns_by_revenue)
- [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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.