Campaigns With Most Clicks

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L3

Asked in

Airbnb

Problem

The ad ops team is identifying which campaigns actually drive engagement. For campaigns that have received at least one click, show the campaign name, total number of clicked impressions, and the highest single-impression revenue, sorted from most clicks to least and settling any tie alphabetically by campaign name.

The campaigns getting all the clicks.

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

A scoping question dressed as an engagement report

Strip the ad ops framing and this is one question about scope: the click count, the top revenue and the list of qualifying campaigns must all be computed over the same rows, the clicked impressions. Anyone can write the grouping. What separates candidates is where the click condition lives. Put WHERE clicked = 1 ahead of GROUP BY and every aggregate sees only clicks. Leave it out and keep campaigns with HAVING SUM(clicked) > 0 instead, and the campaign list still looks right while COUNT(*) counts every impression: the loyalty program reports 84 instead of 71, the brand-awareness campaign 17 instead of 3, and the sort order moves with them.

Build it in three decisions

Step 1: Filter to clicked impressions first

WHERE clicked = 1 decides what every aggregate sees. Because it runs before grouping, it also enforces 'at least one click' for free: a campaign with no clicked rows never forms a group, so there is nothing left for a HAVING to do.

Step 2: Count and take the max per campaign

Group by ad_campaign and emit COUNT(*) AS total_clicks and MAX(revenue) AS max_impression_revenue. After the filter every surviving row is a click, so a plain COUNT(*) is the click count; no conditional counting is needed.

Step 3: Sort by the alias, then the name

ORDER BY total_clicks DESC, ad_campaign puts the busiest campaigns first. Four campaigns tie at 4 clicks on this data, so the second key is the one that settles them alphabetically, exactly as the question asks.

The solution

SELECT
    ad_campaign,
    COUNT(*) AS total_clicks,
    MAX(revenue) AS max_impression_revenue
FROM ad_impressions
WHERE clicked = 1
GROUP BY ad_campaign
ORDER BY total_clicks DESC, ad_campaign

Group everything, filter the groups

COUNT(*) over all impressions with HAVING SUM(clicked) > 0: the right eight campaigns, but the loyalty program shows 84 and brand awareness 17, so brand awareness jumps above the 4-click campaigns.

Filter the rows, then group

WHERE clicked = 1 then COUNT(*): the loyalty program shows 71, brand awareness 3, and the query needs no HAVING at all.

Common follow-up questions

• How would you also show campaigns with zero clicks? (The click condition moves into the aggregate (a conditional count) so the row filter no longer removes campaigns.)
• What if you needed the top 5 campaigns only? (The cut after the fifth row falls inside the 4-click tie, so the tie rule decides who makes the list.)
• How would performance change if click-through rates were 50% instead of sub-1%? (The `WHERE` becomes far less selective, so index-driven skipping stops paying off.)