# Campaign Revenue Totals > Every campaign has a price tag. Total them up. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem The finance team needs campaign-level revenue figures for the annual ad report. Show each campaign and its total revenue. ## Worked solution and explanation ### Why this problem exists in real interviews This is a fundamental GROUP BY question that tests whether you can aggregate to the correct grain. Despite its simplicity, it screens for candidates who understand that `SUM` requires a `GROUP BY` and who can express a clean, minimal query. --- ### Break down the requirements #### Step 1: Group by campaign `GROUP BY ad_campaign` collapses the 100M impression rows into one row per campaign name. #### Step 2: Sum revenue `SUM(revenue)` computes total revenue per campaign. No filtering is needed since the prompt asks for all campaigns. --- ### The solution **Simple group-and-sum** ```sql SELECT ad_campaign, SUM(revenue) AS total_revenue FROM ad_impressions GROUP BY ad_campaign ``` > **Cost Analysis** > > Full table scan of 100M rows reduced to ~180 output rows by the `GROUP BY`. This is a single-pass aggregation with no joins or subqueries. At 100M rows, the scan dominates; a columnar store or covering index on `(ad_campaign, revenue)` would cut I/O. > **Common Pitfall** > > Adding `ORDER BY` when the prompt does not ask for it wastes sort time and shows the candidate is not reading carefully. Conversely, forgetting `GROUP BY` entirely produces a single-row result. --- ## Common follow-up questions - What if campaigns with zero revenue should still appear? _(Tests understanding of NULL handling and whether a dimension table join is needed.)_ - How would you add a row showing the grand total across all campaigns? _(Tests ROLLUP or UNION ALL patterns for summary rows.)_ - What if the revenue column contained NULLs? _(SUM ignores NULLs by default, but the candidate should state this explicitly.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/campaign_revenue_totals) - [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.