Ad Clickers

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
L4

Problem

Which users clicked at least one ad in the ad_impressions log, and how much revenue did those clicks bring in? Show each clicker's username from users alongside their total click revenue, highest contributor first.

Who clicked? What did they spend?

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

users: 10 of 200 rows
user_idusernameemailsignup_dateaccount_statusage_bucket
100alicealice@example.com2025-02-02inactive25-34
197aaron42aaron42@example.com2026-03-03suspended35-44
294 ameliaamelia@example.com2024-04-04pending_verification45-54
391arjun arjun@example.com2025-05-05active55-64
488ava99ava99@example.com2026-06-06inactive65+
585andrew_kandrew_k@example.com2024-07-07suspendedNULL
682anikaanika@example.com2025-08-08 16:34:00pending_verification18-24
779aidenaiden@example.com2026-09-09 14:53:00active25-34
876aria_baria_b@example.com2024-10-10inactive35-44
973brianbrian@example.com2025-11-11suspended45-54

ad_impressions

Columns: impression_id INTEGER, user_id INTEGER, ad_campaign TEXT, impression_time TEXT, clicked INTEGER, revenue REAL

ad_impressions: 10 of 200 rows
impression_iduser_idad_campaignimpression_timeclickedrevenue
253100BRAND_AWARENESS_Q12026-02-02 01:07:000NULL
306197PRODUCT_LAUNCH_X2026-03-03 02:14:000NULL
359294RETARGETING_CART2026-04-04 03:21:000NULL
412391HOLIDAY_PROMO2026-05-05 04:28:000NULL
465488NEW_USER_ACQU2026-06-06 05:35:0010.2
518NULLLOYALTY_PROGRAM2026-07-07 06:42:000NULL
571682FLASH_SALE_48H2026-08-08 07:49:000NULL
624779SUMMER_SALE_20242026-09-09 08:56:000NULL
677876BRAND_AWARENESS_Q12026-01-10 09:03:000NULL
730973PRODUCT_LAUNCH_X2026-02-11 10:10:0010.35

Expected output

First 5 of 16 rows
usernametotal_revenue
emma25.1
dora_7723.55
dahlia22.5
dimitri22
devon21

Worked solution and explanation

Why this problem exists in real interviews

Join a dimension to a filtered fact and sum a metric: this is the most common shape of revenue analytics in the wild. Total purchase value by customer, total click revenue by ad, total session minutes by user. Interviewers use this prompt to check whether you place the filter in WHERE (correct) or in HAVING (wrong scope), whether you join on the right key, and whether you sort by the aggregate with a deterministic tiebreak.

Break down the requirements

Step 1: Join users to ad_impressions on user_id

The dimension (username) lives on users; the fact (revenue, clicked flag) lives on ad_impressions. INNER JOIN on user_id keeps only users who had at least one impression. Users with no impressions should not appear, so INNER is correct.

Step 2: Filter to clicked = 1 in WHERE

WHERE a.clicked = 1 drops unclicked impressions before grouping. This belongs in WHERE because it filters rows, not groups. A user with zero clicks is eliminated at the row level and therefore absent from the grouped output, satisfying 'at least one click.'

Step 3: Group by username, sum revenue

GROUP BY u.username produces one row per user. SUM(a.revenue) totals click revenue. If username is not unique (rare, but possible), GROUP BY user_id is safer; the spec asks for username in the output, so group by username if it is unique by contract.

Step 4: Sort by total_revenue DESC, username ASC

ORDER BY total_revenue DESC, username ASC. The tiebreak on username ASC makes output deterministic for users with identical totals (common when revenue is always the same small set of values).

The solution

Join, filter in WHERE, group, sort with tiebreak
SELECT
    u.username,
    SUM(a.revenue) AS total_revenue
FROM users u
JOIN ad_impressions a ON u.user_id = a.user_id
WHERE a.clicked = 1
GROUP BY u.username
ORDER BY total_revenue DESC, u.username ASC

Common follow-up questions