Top Campaign by User Revenue

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

L5

Asked in

Airbnb

Problem

For each user who clicked an ad, find which campaign generated the most revenue for that user. If multiple campaigns tie for the top, list each qualifying entry only once. Show the user and the top campaign.

Which campaign made each user spend the most?

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

Why this problem exists in real interviews

Per-group top-N is a daily request from analytics. The candidate must recognize that ranking happens within each user (partitioned), not across the whole table, and that ties produce multiple rows the interviewer expects to see deduplicated cleanly.

Break down the requirements

The solution

WITH ranked AS (
  SELECT user_id, ad_campaign,
         DENSE_RANK() OVER (PARTITION BY user_id ORDER BY revenue DESC) AS rnk
  FROM ad_impressions
  WHERE clicked = 1
)
SELECT DISTINCT r.user_id, r.ad_campaign
FROM ranked r
WHERE r.rnk = 1

Common follow-up questions

• If a user clicked ads in campaign A generating $50 and campaign B also generating $50, does your query return both? (Tests tie-handling; the prompt says 'list each qualifying entry only once', so `DENSE_RANK` with rank = 1 returns both tied campaigns.)
• Should the revenue sum include non-clicked impressions for a campaign, or only clicked ones? (Tests whether the WHERE clicked filter applies before aggregation, which it should given the prompt says 'who clicked'.)
• How would the performance change if you filtered clicked rows in a CTE vs. in the WHERE of the final query? (Tests CTE materialization behavior; some engines materialize CTEs, others inline them.)