Return on a Glance

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

General Assembly

Problem

The marketing analytics team is benchmarking every ad campaign on how much each impression earns. For each campaign, give the number of impressions it served and its revenue per impression, where an impression that brought in nothing still counts, from the best earners down.

Every impression costs something. Find the campaigns earning it back.

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 tests

This is revenue per impression served, disguised as a campaign leaderboard. The skill being probed: do you know that AVG(revenue) quietly drops every NULL-revenue impression before it divides, so it answers 'average revenue among impressions that earned something', not revenue per impression served? Across the table only about one impression in seven carries any revenue, so AVG(revenue) divides by that earning minority and reports a figure several times too high. Fold every empty impression to a 0 with AVG(COALESCE(revenue, 0)) and the average divides by all served impressions, which is exactly why you also surface COUNT(*): it is the denominator the figure rests on. Reach for AVG(revenue) and every campaign inflates by a different amount, so the whole ranking reshuffles.

Why AVG(revenue) betrays you

AVG(revenue)

Averages only the impressions where revenue is not NULL, because AVG skips nulls before it divides. With most impressions earning nothing, it divides by the earning minority alone and reports a figure far above revenue per impression. It answers 'average revenue among impressions that earned something', a question nobody asked.

AVG(COALESCE(revenue, 0))

Folds every empty impression to a 0 before averaging, so the divisor is every impression served. This is revenue per impression, the figure the team compares across campaigns. It equals SUM(revenue) over COUNT(*) written as one aggregate, and COUNT(*) is the impression total worth reporting alongside it.

Take LOYALTY_PROGRAM: across all 84 impressions it served, AVG(COALESCE(revenue, 0)) divides its total revenue by all 84 and returns 2.5637. Swap in AVG(revenue) and the divisor collapses to just the impressions that earned something, so the same campaign reports a number several times larger. Every campaign inflates this way, and because they inflate by different amounts, the ranking itself moves, not just the values.

Building it

Step 1: Count the impressions per campaign

GROUP BY ad_campaign sets the grain to one row per campaign, and COUNT(*) on each group is the number of impressions that campaign served. That same count is the denominator the per-impression average has to divide by, so surfacing it makes the benchmark auditable at a glance.

Step 2: Fold empty impressions to zero

AVG divides by however many rows it sees, but it never sees a NULL-revenue row. Wrap each revenue in COALESCE(revenue, 0) so an empty impression enters the average as a real 0 and stays in the denominator. That single change turns 'average of the earners' into 'revenue per impression served'.

Step 3: Order highest first

ORDER BY avg_revenue_per_impression DESC puts the best earners on top. The campaign averages come out distinct, so the sort is already a total order and needs no tiebreaker.

The solution

SELECT ad_campaign,
       COUNT(*) AS impressions,
       ROUND(AVG(COALESCE(revenue, 0)), 4) AS avg_revenue_per_impression
FROM ad_impressions
GROUP BY ad_campaign
ORDER BY avg_revenue_per_impression DESC;

Common follow-up questions

• Product wants both the share of impressions that converted and the average value of a converting impression per campaign. How does that change your numerators and denominators? (Tests whether the candidate can separate propensity to convert from average order value.)
• If `ad_campaign` arrives with trailing whitespace or mixed casing, how would you keep the same logical campaign from splitting into several rows? (Tests text-normalization awareness that silently fragments the grouping key.)
• Marketing only trusts the figure for campaigns with at least 1,000 impressions. Where does that filter on `COUNT(*)` go, and why not in `WHERE`? (Tests adding a volume threshold without breaking the aggregate.)
• How would you turn this into a rolling twelve-month benchmark using `impression_time`, and how does partitioning on it help? (Tests time-scoped benchmarking on a partitioned table.)