The Campaign Trail

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

Asked in

Uber

Problem

The ad_impressions table logs every ad shown along with the revenue it earned, so a campaign's money is spread across many rows. Return the campaigns whose revenue booked through the end of June 2026 adds up to more than $4.50, listed alphabetically by name.

Impressions are vanity. Conversions are sanity.

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

This is a filter-the-total problem wearing a renewal-review costume. Each campaign's money is scattered across a few clicked impressions worth a couple of dollars apiece, and no single row on this data earns more than $3.75, so the $4.50 bar is a property of the campaign's sum, never of an impression. Anyone can write the grouping. What separates candidates is judging the bar after the totals exist while keeping the June cutoff in front of them. Put the bar in WHERE revenue > 4.5 and the list comes back empty. Drop the cutoff and 'HOLIDAY_PROMO' sneaks in on July money it had not earned yet.

Three moves, in the order the engine runs them

Step 1: Cut the timeline at June 2026

impression_time is ISO text, so strftime('%Y-%m', impression_time) yields a 'YYYY-MM' key that compares correctly as a string. Keep rows <= '2026-06'. This belongs before the grouping because it decides which rows a total is made of.

Step 2: Total each campaign

Group by ad_campaign and take SUM(revenue). Unclicked impressions carry NULL revenue and SUM skips them, which is exactly the business meaning: they earned nothing.

Step 3: Judge the total, then sort

HAVING SUM(revenue) > 4.5 runs once per campaign, after the sum exists. It is strictly greater, so a campaign at exactly $4.50 stays out. Finish with ORDER BY ad_campaign; the names are one per group, so the order is fully decided.

SELECT ad_campaign
FROM ad_impressions
WHERE strftime('%Y-%m', impression_time) <= '2026-06'
GROUP BY ad_campaign
HAVING SUM(revenue) > 4.5
ORDER BY ad_campaign

Bar on each impression

WHERE revenue > 4.5 asks whether one impression earned $4.50. None did; the largest single row is $3.75, so the answer is an empty list.

Bar on each campaign

HAVING SUM(revenue) > 4.5 asks whether the campaign earned $4.50 in total. 'PRODUCT_LAUNCH_X' gets there with $5.00 from four rows, none of them above $2.75.

Common follow-up questions

• How would you return each qualifying campaign's total next to its name? (Aliasing `SUM(revenue)` once in the select list and reading it in the bar instead of computing it twice.)
• Renewal now goes to the top three campaigns by revenue through June. What changes? (Moves from a fixed bar to ordering by the total with a limit, and asks how a tie at the cut is settled.)
• What if `revenue` were stored as integer cents? (Tests whether the bar becomes `450` and whether any averaging would hit integer division.)