Active 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

The advertising team is reviewing campaign performance for the quarterly deck, and only campaigns that served more than 15 impressions make the cut. For each of those, show the impressions served, the total revenue earned, and the click-through rate as a percentage rounded to one decimal place, ordered from the highest click-through rate down to the lowest, and by campaign name where two rates land the same.

Which campaigns are earning their keep?

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 is really testing

Under the campaign-performance costume, this is a grouped ratio with a volume floor: click-through rate is clicks divided by impressions per campaign, and you only rank campaigns that cleared enough impressions to trust the rate. Everyone gets the GROUP BY and the COUNT. The details that separate candidates are quieter. The volume floor is a filter on COUNT(*), a number that only exists after grouping, so it has to live in HAVING, not WHERE: drop it and a campaign with two impressions and one click reports a 50 percent rate and jumps to the top of a ranking meant to surface real performers. The division has to be forced into floating point: miss it and the whole click-through column comes back a wall of zeros you will not notice until someone asks why every campaign converts at 0 percent. And two campaigns tie at exactly 25.0 percent, so the ordering needs a name tie-break or their positions shuffle between runs.

Building it up

Step 1: Collapse to one row per campaign

There are about 200 campaigns across 100M rows, so ad_campaign is the grain. Collapse the rows with GROUP BY ad_campaign, then COUNT(*) gives impressions and SUM(revenue) gives total_revenue. SUM ignores NULL revenue rows automatically, which matters here because roughly a third of revenue values are NULL: you want clicks that earned nothing to count as impressions but contribute zero to the revenue total, and plain SUM does exactly that.

Step 2: Compute the rate, in floating point

Click-through rate is 100.0 * SUM(clicked) / COUNT(*), wrapped in ROUND(..., 1) for one decimal place. The 100.0 keeps it in floating point (see the pitfall above); ROUND only formats the final number, it does not fix integer division, so the float literal still has to be there.

Step 3: Filter on the count, then order

HAVING COUNT(*) > 15 drops thin campaigns AFTER grouping, since the threshold is on the aggregate, not on individual rows. This is the load-bearing filter: two seed campaigns sit at 15 impressions and get removed, and their volatile rates would otherwise pollute the ranking. A useful side effect: it guarantees the denominator is at least 16, so there is no division-by-zero to guard against. Then ORDER BY ctr DESC puts the best converters first, with ad_campaign as a deterministic tie-break that earns its keep here: two surviving campaigns land on exactly 25.0 percent, so the name is what decides their order, alphabetically.

The solution

SELECT
    ad_campaign,
    COUNT(*) AS impressions,
    SUM(revenue) AS total_revenue,
    ROUND(100.0 * SUM(clicked) / COUNT(*), 1) AS ctr
FROM ad_impressions
GROUP BY ad_campaign
HAVING COUNT(*) > 15
ORDER BY ctr DESC, ad_campaign

Common follow-up questions

• What does `HAVING COUNT(*) > 15` actually buy you here, and how would you choose the threshold if tiny campaigns were producing noisy 100 percent or 0 percent rates? (Tests whether the candidate understands that the threshold is on the aggregate and how it interacts with rate stability.)
• If you needed click-through rate broken down by both campaign and geographic region, how would you restructure the grouping and what happens to the row count? (Tests whether the candidate can add a second grouping dimension and reason about grain.)
• About a third of revenue values are NULL. Walk through how `SUM(revenue)` treats them, and what changes if you wanted those rows excluded from the impression count too. (Tests understanding of NULL handling in aggregation versus filtering.)
• The table is partitioned on `impression_time` but this query scans all of it. What one change limits the scan to a single quarter, and why does it help? (Tests whether the candidate can reason about partition pruning on a large table.)