Top 2 Ad Campaigns by Spend

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

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.

Two campaigns. Most of the budget.

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 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

SELECT
    ad_campaign,
    SUM(revenue) AS total_revenue
FROM ad_impressions
GROUP BY ad_campaign
ORDER BY total_revenue DESC
LIMIT 2

SELECT
    ad_campaign,
    revenue
FROM ad_impressions
ORDER BY revenue DESC
LIMIT 2

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.)