High-Spend 2025 Campaigns

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

L3

Problem

A campaign only counts as earning in a calendar year if its impressions brought in more than five dollars across that year. From ad_impressions, we need every campaign and year that cleared that bar, with how many different people the campaign reached in that year.

Big-budget campaigns from last year.

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 asks

Strip off the earnings-review costume and this is a grouped sum gated by a threshold, keyed on two things at once: the campaign and the calendar year hiding inside a text timestamp. Anyone can write SUM(revenue). What separates candidates is where each piece lands. The year has to become a grouping key, the five-dollar bar has to run after the totals exist, and reach has to dedupe repeat visitors. Group by campaign alone and you get one blended total per campaign that hides which years earned; count rows and you report impressions wearing a reach label.

Step 1: Pull the year out of the timestamp

impression_time is a full timestamp stored as text. EXTRACT(YEAR FROM impression_time) turns it into the calendar year. Campaign names like 'SUMMER_SALE_2024' contain a year too, and that is bait: the year that counts is when the impression was served.

Step 2: Group on campaign and year together

GROUP BY ad_campaign, EXTRACT(YEAR FROM impression_time) gives one bucket per campaign per year. Drop the year and every year blends into one total; group by the raw impression_time instead and each bucket shrinks to a single impression.

Step 3: Two aggregates, two jobs

SUM(revenue) lives in HAVING, because the bar is on the group's total. COUNT(DISTINCT user_id) is the reach you select: the same person is served a campaign several times in a year, and the dedupe folds those repeats to one. Both aggregates skip NULLs, so impressions with no revenue add nothing and a NULL user_id adds no person.

SELECT ad_campaign,
       EXTRACT(YEAR FROM impression_time) AS impression_year,
       COUNT(DISTINCT user_id) AS unique_users
FROM ad_impressions
GROUP BY ad_campaign, EXTRACT(YEAR FROM impression_time)
HAVING SUM(revenue) > 5

SELECT ad_campaign,
       EXTRACT(YEAR FROM impression_time) AS impression_year,
       COUNT(*) AS unique_users
FROM ad_impressions
GROUP BY ad_campaign, EXTRACT(YEAR FROM impression_time)
HAVING SUM(revenue) > 5

Counting impressions

COUNT(*) tallies every row in the group, so a user shown a campaign five times that year reports a reach of 5, and NULL user_id rows count too.

Counting people

COUNT(DISTINCT user_id) folds each user's impressions in the group to one and drops NULL user_id, so the number means how many different people saw the campaign that year.

Common follow-up questions

• Now also return each campaign-year's total revenue, highest first. (Tests selecting the aggregate itself and sorting on it.)
• How would you list every campaign-year with a column saying whether it cleared the bar? (Tests keeping a group the bar removes, with a flag instead of a filter.)
• What changes if user identity lives in a separate `users` table and impressions only store a device id? (Pushes toward a join before the distinct count and deduping identity across devices.)