# Ad Revenue 2026

> Annual ad revenue. On the books.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The ad sales team is closing out the books on 2026 and needs the annual numbers by campaign. Total the revenue each campaign brought in, biggest earners first.

## Worked solution and explanation

### What this really is

Behind the annual-report framing this is a filtered group-and-sum. The only real decisions are two: how do you pin the aggregate to a single calendar year when the source column is a full timestamp, and what happens to the impressions that never earned a cent. Miss the year filter and last year's SUMMER_SALE_2024 (9.00) walks straight onto your leaderboard above every real campaign.

> **Pin the year on the timestamp**
>
> impression_time is a datetime, not a year. In this SQLite sandbox you carve the year out with strftime('%Y', impression_time) and compare it to the string '2026'. That one predicate is the whole game: it decides which rows the SUM even sees.

### Building it

#### Step 1: Scope to the year

The table mixes years on purpose. SUMMER_SALE_2024 sits in 2024 and RETARGETING_CART in 2025. The predicate strftime('%Y', impression_time) = '2026' keeps only the current year's impressions, so those two never reach the total.

#### Step 2: Sum revenue per campaign

Group by ad_campaign and SUM(revenue). SUM quietly skips NULLs, so the unclicked impressions that carry no revenue neither break the sum nor drag a campaign to zero. HOLIDAY_PROMO keeps its 8.00 even with a NULL-revenue row in the mix.

#### Step 3: Order the leaderboard

The ask is biggest earners first, so ORDER BY total_revenue DESC. HOLIDAY_PROMO (8.00) leads and FLASH_SALE_48H (1.00) trails.

**Revenue by campaign for the year**

```sql
SELECT
  ad_campaign,
  SUM(revenue) AS total_revenue
FROM ad_impressions
WHERE strftime('%Y', impression_time) = '2026'
GROUP BY ad_campaign
ORDER BY total_revenue DESC
```

*One filtered aggregate: the year predicate scopes it, SUM ignores the NULL-revenue rows.*

> **EXTRACT and COUNT traps**
>
> Two ways people lose this. First, reaching for EXTRACT(YEAR FROM impression_time): valid Postgres, but this sandbox is SQLite and it throws. Use strftime. Second, using COUNT(revenue) or coercing NULLs to 0 before summing: unnecessary, since SUM already ignores NULLs, and COUNT answers a different question.

> **The distractor years**
>
> The tell an interviewer watches for is whether you notice the off-year rows at all. A candidate who returns SUMMER_SALE_2024 at the top has summed across all time and never scoped the year. The strong candidate calls out the mixed years before writing a line.

**No year filter**

SUM across every year ranks SUMMER_SALE_2024 (9.00) and RETARGETING_CART (7.00) on top: a report for years that already closed.

**Scoped to 2026**

The strftime predicate drops both, leaving four real campaigns led by HOLIDAY_PROMO at 8.00.

## Common follow-up questions

- How would you show every month's revenue within the year instead of a single per-campaign total? _(Tests grouping on strftime('%m', impression_time).)_
- A campaign ran but earned nothing all year. Should it appear with 0 or be omitted, and how do you make that happen? _(Distinguishes SUM NULL behavior from an explicit COALESCE plus a LEFT JOIN against a campaign list.)_
- How does the query change if revenue is stored in cents as an integer? _(Checks awareness of integer math and formatting for a money report.)_

## Related

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