# Return on a Glance > Every impression costs something. Find the campaigns earning it back. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem The marketing analytics team is benchmarking how much revenue each ad campaign earns per impression, where every impression served counts toward the average even the ones that brought in nothing. Give each campaign that figure, highest first. ## Worked solution and explanation ### What this really tests This is a per-impression average wearing a per-sale average's clothes. The skill being probed: do you know that SQL's AVG(revenue) quietly throws away every NULL-revenue row before it divides? Reach for AVG(revenue) here and a campaign with one 0.35 sale across 10,000 served impressions reports 0.35, because AVG divided by 1, not by 10,000. The benchmark marketing asked for is revenue per impression served, so the denominator has to be every impression, not just the ones that earned money. --- ### Why AVG(revenue) betrays you **AVG(revenue)** Averages only the rows where revenue IS NOT NULL. One 0.35 sale among 10,000 impressions reports 0.35. It answers 'average revenue among impressions that earned something', a question nobody asked. **SUM(revenue) / COUNT(*)** Divides total revenue by every impression served. The same campaign reports 0.000035. This is revenue per impression, the figure the team actually wants to compare across campaigns. In the sample data BRAND_AWARENESS_Q1 has two impressions and neither earned revenue, so it correctly lands at 0.0. AVG(revenue) would return NULL there, since it has no non-NULL rows to average at all. --- ### Building it #### Step 1: Collapse to one row per campaign GROUP BY ad_campaign is the output grain: the team wants one benchmark figure per campaign, so every impression folds into its campaign's bucket before any math happens. #### Step 2: Sum revenue, guard the empty case SUM(revenue) totals the earnings in each bucket and already ignores NULLs for you. Wrap it in COALESCE(..., 0) so a campaign whose every impression earned nothing reports 0 instead of a NULL total. #### Step 3: Divide by all impressions, then order COUNT(*) counts every impression in the bucket, NULL revenue included, which is the whole point. Multiply the sum by 1.0 first so the division stays floating point, then order highest first with ad_campaign as a stable tiebreaker for the campaigns that tie at 0. --- ### The solution **Revenue per impression, per campaign** ```sql SELECT ad_campaign, ROUND(COALESCE(SUM(revenue), 0) * 1.0 / COUNT(*), 4) AS avg_revenue_per_impression FROM ad_impressions GROUP BY ad_campaign ORDER BY avg_revenue_per_impression DESC, ad_campaign; ``` > **Common pitfall** > > The reflex answer AVG(revenue) is not just imprecise here, it is a different metric. It silently drops NULL-revenue rows from the denominator, so sparse-converting campaigns look far more valuable than they are. This is the single mistake that separates a correct answer from a plausible-looking wrong one. > **Interviewers watch for** > > A strong candidate asks out loud whether zero-revenue impressions belong in the denominator before writing a line. That one question tells the interviewer you understand AVG's NULL semantics without being prompted. > **Cost analysis** > > The table is 150M rows (29 GB), partitioned on impression_time. This query scans and buckets by ad_campaign, which has only ~100 distinct values, so the grouped result stays tiny and the aggregation streams in a single pass. No join, no subquery, no sort blow-up. ## Common follow-up questions - Product wants both the conversion rate 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 go, and why not in WHERE? _(Tests ability to add a volume threshold without breaking the aggregate.)_ - How would you restrict this to last quarter's impressions, and how does the impression_time partitioning help? _(Tests understanding of time-scoped benchmarking on a partitioned table.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/return_on_a_glance) - [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.