Campaign Revenue Totals

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

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.

Every campaign has a price tag. Total them up.

Tables

ad_impressions

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

Expected output

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.

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.

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.)