Ad Revenue 2026

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

Domain: SQL
Difficulty: easy
Seniority: L3

Problem

Which campaigns brought in the most revenue in 2026, going by the impressions logged in ad_impressions? The ad sales team is closing that year's books and wants every campaign's total, biggest earners first.

Annual ad revenue. On the books.

Tables

ad_impressions

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

ad_impressions: 10 of 200 rows
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
LOYALTY_PROGRAM	27.85
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 year-end framing this is a one-year filtered group-and-sum, and the year lives in two places that disagree. impression_time says when an impression was served; a name like SUMMER_SALE_2024 says nothing about it. Anyone can write the SUM. What separates candidates is scoping rows by the timestamp before summing. Skip that and LOYALTY_PROGRAM absorbs five already-settled years, reporting 215.35 instead of 27.85. Trust the name instead and you throw away the year's second-biggest earner.

Building it

Step 1: Scope to the year on `impression_time`

The table spans six years. Most prior-year rows are unclicked impressions with no revenue, which is exactly why the bug hides: only LOYALTY_PROGRAM has paid impressions in every earlier month. Filter on strftime('%Y', impression_time) = '2026' before grouping, and those 60 rows never reach a total.

Step 2: Sum `revenue` per `ad_campaign`

Group by ad_campaign and take SUM(revenue). About half the rows are unclicked impressions whose revenue is NULL. SUM skips them, so no COALESCE is needed and no campaign collapses to NULL. SUMMER_SALE_2024 stays in: its 2026 impressions earned 16.70.

Step 3: Put the biggest earners first

Sort by total_revenue descending. LOYALTY_PROGRAM leads at 27.85, SUMMER_SALE_2024 follows at 16.70, and RETARGETING_CART closes the list at 3.40. No two campaigns share a total, so no tiebreaker is needed.

Revenue by campaign for the year

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 timestamp predicate scopes it, and SUM ignores the NULL revenue rows.

Every year summed: LOYALTY_PROGRAM reads 215.35, and the other seven rows match the right answer to the cent, because their earlier-year impressions carry no revenue. The one wrong number sits at the top of the report.
Scoped to 2026: LOYALTY_PROGRAM drops to 27.85, the revenue its 2026 impressions actually earned. It is still first, but now by a believable margin over SUMMER_SALE_2024.

Common follow-up questions

How would you show each campaign's revenue month by month within 2026? (Tests adding a second key derived from `impression_time` to the grouping.)
A campaign ran in 2026 but never earned a cent. Should it appear with 0, and how? (Checks that the candidate knows `SUM` over only `NULL` values returns `NULL`, and whether they reach for `COALESCE`.)
Finance closes on a fiscal year that starts in July. What changes? (Tests moving the year boundary off the calendar, which a range predicate handles cleanly.)