# High-Spend 2025 Campaigns > Big-budget campaigns from last year. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem The ad sales team is closing out the 2025 books for the year-end review. Find every campaign that brought in more than five dollars of revenue that year, and next to each campaign name show how many different users it reached, sorted alphabetically by name. ## Worked solution and explanation ### What this really asks Strip off the year-end leaderboard costume and this is a grouped sum gated by a threshold, with a count of unique users riding alongside it. Three things have to land in the right place. The time window belongs before the rows collapse, in WHERE. The revenue threshold belongs after the sum exists, in HAVING. And the reach figure has to dedupe repeat visitors or it silently reports impressions instead of people. Candidates who blur any of those are the ones who fail, and the seed is built to punish exactly that. > **Two filters, two stages** > > Row-level conditions (which year an impression was served) go in WHERE and run before grouping. Group-level conditions (does the campaign's total revenue clear five dollars) go in HAVING and run after the SUM exists. There is no version of SUM(revenue) you can reference in WHERE, because at WHERE time the rows have not been collapsed yet. #### Step 1: Filter to the target year first impression_time is a full timestamp, so you extract the year with strftime('%Y', impression_time) and compare it to '2025' as a string. Doing this in WHERE means every row from other years is gone before any grouping work happens, which is both correct and cheap. Note the seed mixes campaigns like SUMMER_SALE_2024 whose NAME contains a year. That is bait. The year lives in the timestamp, not in the campaign string. #### Step 2: Collapse to one row per campaign GROUP BY ad_campaign turns the surviving impressions into one row per campaign. The same campaign name appears on many impressions across the year, and grouping is what folds those repeated rows into a single bucket you can aggregate over. #### Step 3: Two aggregates, two jobs SUM(revenue) totals each campaign's earnings and lives in HAVING, because the five-dollar bar is a condition on that total. COUNT(DISTINCT user_id) is the reach figure you select: the same person gets served a campaign many times, so a plain COUNT(*) would report impressions, not people. DISTINCT collapses those repeats to one per user. Both SUM and COUNT quietly skip NULLs, so non-converting impressions and the null user_id rows simply do not contribute. **Canonical solution** ```sql SELECT ad_campaign, COUNT(DISTINCT user_id) AS unique_users FROM ad_impressions WHERE strftime('%Y', impression_time) = '2025' GROUP BY ad_campaign HAVING SUM(revenue) > 5 ORDER BY ad_campaign ``` *WHERE scopes the year before grouping; HAVING applies the revenue threshold after the SUM exists; COUNT(DISTINCT user_id) reports people, not impressions.* > **The classic WHERE-versus-HAVING slip** > > Writing WHERE SUM(revenue) > 5 is the mistake that ends this question. The engine rejects it because aggregates do not exist at WHERE time. The fix is not to wrestle the syntax, it is to recognize that a condition on a total is a condition on a GROUP, and group conditions live in HAVING. **COUNT(*) counts impressions** COUNT(*) tallies every surviving row, so a campaign shown to one loyal user fifty times reports a reach of 50. That is impression volume wearing a reach label, and it also happily counts the null user_id row. **COUNT(DISTINCT user_id) counts people** COUNT(DISTINCT user_id) folds a user's many impressions down to one and drops the null user_id entirely, so the number actually means how many different people saw the campaign. > **What earns the senior nod** > > The tell is whether you say out loud, unprompted, that DISTINCT is doing real work here and that both COUNT and SUM ignore NULLs. The seed makes revenue NULL on almost every impression and leaves some user_id NULL on purpose. A candidate who confirms 'repeat impressions collapse to one user, nulls just get skipped, neither breaks the math' is reasoning about the data, not only the syntax. > **In production** > > Year-string comparison via strftime is fine on a small seed, but on a real impressions table it cannot use an index on impression_time. In a warehouse you would filter on a half-open timestamp range (impression_time >= '2025-01-01' AND impression_time < '2026-01-01') so the planner can range-scan instead of computing a function on every row. COUNT(DISTINCT user_id) at billions of rows is also where you start reaching for approximate distinct counts like HyperLogLog. ## Common follow-up questions - Now also return each surviving campaign's total revenue next to the name and reach, highest revenue first. _(Tests selecting the aggregate itself and ordering by an aggregate rather than alphabetically.)_ - How would the query change if user identity lived on a separate users table and impressions only stored a device id? _(Pushes the candidate toward a join before the distinct count and how to dedupe identity across devices.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/high_spend_year_1_campaigns) - [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.