# Campaign Revenue Totals

> Every campaign has a price tag. Total them up.

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

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

## Problem

Every impression logged in `ad_impressions`, across all the years on record, counts toward the finance team's campaign revenue report. Show each campaign with its total revenue.

## 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 8 rows:

| ad_campaign | total_revenue |
| --- | --- |
| BRAND_AWARENESS_Q1 | 3.6 |
| FLASH_SALE_48H | 5.6 |
| HOLIDAY_PROMO | 6.2 |
| LOYALTY_PROGRAM | 215.35 |
| NEW_USER_ACQU | 4.4 |

## Worked solution and explanation

### What this really is

This is a grain question in a finance costume. 'Total revenue' sounds like one number, and that reading is the trap. A candidate who sums the whole column, or puts `ad_campaign` next to `SUM(revenue)` without telling the engine where one campaign ends, hands finance a single figure. On this data that is about $260 of revenue stamped onto one arbitrary campaign name, and the other seven campaigns vanish from the report. **The skill is fixing the output grain, one row per campaign, before you write the sum.**

### Getting there

#### Step 1: Fix the grain first

The report wants one row per campaign, so `ad_campaign` is the key every output row collapses to. Say that out loud before you touch the metric. It is the decision the whole query hangs on, and it is the one the trap skips.

#### Step 2: Sum `revenue` and let the gaps add nothing

`SUM(revenue)` totals each campaign. About half the impressions never clicked and carry a `NULL` revenue. `SUM` skips them, so those rows add nothing, and the campaign still appears with what its paying impressions earned. You need no filter and no default.

#### Step 3: Keep every year

Nothing in the ask narrows the window. The preview's 215.35 for the loyalty campaign only adds up across every year in `impression_time`. A `2026` filter here returns a smaller number, and it is wrong.

**One row per campaign**

```sql
SELECT ad_campaign, SUM(revenue) AS total_revenue
FROM ad_impressions
GROUP BY ad_campaign
```

**No grouping**

`SELECT ad_campaign, SUM(revenue) FROM ad_impressions` returns 1 row: the grand total beside whichever campaign name the engine read last.

**Grouped by campaign**

Adding `GROUP BY ad_campaign` returns 8 rows, each campaign beside only its own revenue.

> **One row means the grain is lost**
>
> SQLite runs the ungrouped version without complaint, and stricter engines reject it outright. Either way, if your result has one row where you expected eight, the grouping is missing. A wrong number is not the only sign of that mistake.

> **Say how the missing revenue is handled**
>
> Nobody needs `COALESCE(revenue, 0)` here, and it changes nothing. The senior move is saying why: `SUM` ignores `NULL`. A campaign whose impressions were all `NULL` would still appear, but with a `NULL` total rather than 0.

> **One pass, eight output rows**
>
> This is a single scan feeding a hash aggregate keyed on `ad_campaign`. With eight campaigns the hash table is tiny. At production scale the scan dominates, and what keeps it cheap is a columnar layout that reads only `ad_campaign` and `revenue`.

## Common follow-up questions

- What if campaigns with no impressions at all should still appear? _(Tests whether the candidate reaches for a campaign dimension table and an outer join.)_
- How would you add a row showing the grand total across all campaigns? _(Tests `ROLLUP` or `UNION ALL` patterns for summary rows.)_
- Should a campaign with no paying impressions show 0 or `NULL`? _(`SUM` over only `NULL` values returns `NULL`; tests whether `COALESCE` is a deliberate choice.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/campaign_revenue_totals)
- [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.