# Top 2 Ad Campaigns by Spend > Two campaigns. Most of the budget. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L4 · Asked in: General Assembly ## Problem Return the two ad campaigns with the highest total spend, largest first. A media analyst is pulling this from `ad_impressions`, where each clicked impression records what the advertiser paid in `revenue`. ## Tables ### ad_impressions - impression_id INTEGER - user_id INTEGER - ad_campaign TEXT - impression_time TEXT - clicked INTEGER - revenue REAL Sample rows (10 of 200): | impression_id | user_id | ad_campaign | impression_time | clicked | revenue | | --- | --- | --- | --- | --- | --- | | 253 | 100 | BRAND_AWARENESS_Q1 | 2026-02-02 01:07:00 | 0 | NULL | | 306 | 197 | PRODUCT_LAUNCH_X | 2026-03-03 02:14:00 | 0 | NULL | | 359 | 294 | RETARGETING_CART | 2026-04-04 03:21:00 | 0 | NULL | | 412 | 391 | HOLIDAY_PROMO | 2026-05-05 04:28:00 | 0 | NULL | | 465 | 488 | NEW_USER_ACQU | 2026-06-06 05:35:00 | 1 | 0.2 | | 518 | NULL | LOYALTY_PROGRAM | 2026-07-07 06:42:00 | 0 | NULL | | 571 | 682 | FLASH_SALE_48H | 2026-08-08 07:49:00 | 0 | NULL | | 624 | 779 | SUMMER_SALE_2024 | 2026-09-09 08:56:00 | 0 | NULL | | 677 | 876 | BRAND_AWARENESS_Q1 | 2026-01-10 09:03:00 | 0 | NULL | | 730 | 973 | PRODUCT_LAUNCH_X | 2026-02-11 10:10:00 | 1 | 0.35 | ## Expected output First 2 of 2 rows: | ad_campaign | total_revenue | | --- | --- | | LOYALTY_PROGRAM | 215.35 | | SUMMER_SALE_2024 | 16.7 | ## Worked solution and explanation ### What this really is This is a grain problem dressed up as a marketing leaderboard. `ad_impressions` holds one row per impression, and `revenue` is what that single impression earned, filled only when it was clicked. The board lives at the campaign grain, so the real work is collapsing impressions into one total per `ad_campaign` before anything is sorted or cut. Anyone can write a sort with a cut-off. What separates candidates is **where the cut lands**: after the totals, or straight on the raw rows. Sort raw rows and you get the two biggest single payments. On this data both are '`LOYALTY_PROGRAM`' at 4.50, so one campaign appears twice and '`SUMMER_SALE_2024`' never shows up. ### Getting there #### Step 1: Fix the grain before writing anything One row per campaign means `ad_campaign` is the only key you keep. Carry `impression_id` or `impression_time` along and a campaign splits into fragments, and the totals stop meaning anything. #### Step 2: Total the spend per `ad_campaign` `SUM(revenue)` per campaign. Unclicked impressions hold `NULL` in `revenue`, and `SUM` skips them, which is exactly the business rule: nobody paid for an impression nobody clicked. No `COALESCE` is needed, because every campaign has at least one paid impression. #### Step 3: Sort the totals, then cut to two Order by `total_revenue` descending and keep two rows. The board reads 215.35, 16.70, then 6.20 for '`HOLIDAY_PROMO`', so the cut lands on a clear gap and `LIMIT 2` needs no tiebreaker. ### The solution **Total spend per `ad_campaign`, top two** ```sql SELECT ad_campaign, SUM(revenue) AS total_revenue FROM ad_impressions GROUP BY ad_campaign ORDER BY total_revenue DESC LIMIT 2 ``` **The raw-row sort that looks right** ```sql SELECT ad_campaign, revenue FROM ad_impressions ORDER BY revenue DESC LIMIT 2 ``` *Returns two 4.50 payments, both from '`LOYALTY_PROGRAM`': individual impressions, not campaign totals.* > **Counting impressions measures volume, not money** > > `COUNT(*)` per campaign also looks like a leaderboard. Here it even picks the same two names, which is why it survives a glance at the output. Its column is 84 and 21 impressions, though, not 215.35 and 16.70 in spend. Spend lives in `revenue`. > **Say the grain out loud first** > > Strong candidates say 'one row per campaign' before typing and then check that the `GROUP BY` holds exactly `ad_campaign`. That one sentence tells the interviewer the `LIMIT` will land on totals, not on impressions. > **The sort runs on a couple of hundred rows** > > In production `ad_impressions` runs to roughly 300 million rows across a couple of hundred campaigns. The aggregate is one scan feeding a small hash table, and the sort only sees the grouped rows, so `ORDER BY` plus `LIMIT` is effectively free. Once the ask gains a date window, the lever is pruning on the `impression_time` partitions. ## Common follow-up questions - Two campaigns now tie for second place. How do you return both? _(Moves the cut from `LIMIT` to a tie-aware ranking over the totals.)_ - Internal campaigns whose names contain 'test' must be left off. Does that filter go before or after the totals? _(Row filter on `ad_campaign` before aggregation versus a filter on aggregated rows.)_ - Spend should cover only the current year. What changes, and what does it cost on a table partitioned by `impression_time`? _(Date window plus partition pruning.)_ - Is `revenue` what the advertiser paid or what the platform kept? How would you confirm before shipping? _(Schema-reading discipline on a column whose name and business meaning can drift.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/top_2_ad_campaigns_by_spend) - [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.