Users Who Clicked Ads
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
- Meta
Problem
The ad retargeting pipeline needs a seed list of users who have clicked at least one ad. Return their user IDs.
Ad clickers and their account details.
Tables
ad_impressions
Columns: impression_id INTEGER, user_id INTEGER, ad_campaign TEXT, impression_time TEXT, clicked INTEGER, revenue REAL
| impression_id | user_id | ad_campaign | impression_time | clicked | revenue |
|---|---|---|---|---|---|
| 253 | 100 | BRAND_AWARENESS_Q1 | 2026-02-02 01:07:00 | 0 | NULL |
| 306 | 197 | PRODUCT_LAUNCH_X | 2026-03-03 02:14:00 | 0 | NULL |
| 359 | 294 | RETARGETING_CART | 2026-04-04 03:21:00 | 0 | NULL |
| 412 | 391 | HOLIDAY_PROMO | 2026-05-05 04:28:00 | 0 | NULL |
| 465 | 488 | NEW_USER_ACQU | 2026-06-06 05:35:00 | 1 | 0.2 |
| 518 | NULL | LOYALTY_PROGRAM | 2026-07-07 06:42:00 | 0 | NULL |
| 571 | 682 | FLASH_SALE_48H | 2026-08-08 07:49:00 | 0 | NULL |
| 624 | 779 | SUMMER_SALE_2024 | 2026-09-09 08:56:00 | 0 | NULL |
| 677 | 876 | BRAND_AWARENESS_Q1 | 2026-01-10 09:03:00 | 0 | NULL |
| 730 | 973 | PRODUCT_LAUNCH_X | 2026-02-11 10:10:00 | 1 | 0.35 |
users
Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT
| user_id | username | signup_date | account_status | age_bucket | |
|---|---|---|---|---|---|
| 100 | alice | alice@example.com | 2025-02-02 | inactive | 25-34 |
| 197 | aaron42 | aaron42@example.com | 2026-03-03 | suspended | 35-44 |
| 294 | amelia | amelia@example.com | 2024-04-04 | pending_verification | 45-54 |
| 391 | arjun | arjun@example.com | 2025-05-05 | active | 55-64 |
| 488 | ava99 | ava99@example.com | 2026-06-06 | inactive | 65+ |
| 585 | andrew_k | andrew_k@example.com | 2024-07-07 | suspended | NULL |
| 682 | anika | anika@example.com | 2025-08-08 16:34:00 | pending_verification | 18-24 |
| 779 | aiden | aiden@example.com | 2026-09-09 14:53:00 | active | 25-34 |
| 876 | aria_b | aria_b@example.com | 2024-10-10 | inactive | 35-44 |
| 973 | brian | brian@example.com | 2025-11-11 | suspended | 45-54 |
Expected output
| user_id |
|---|
| 488 |
| 973 |
| NULL |
| 2137 |
| 2234 |
Worked solution and explanation
Why this problem exists in real interviews
This tests a simple filter with DISTINCT. Interviewers check whether you can identify users meeting a condition and deduplicate the result.
Break down the requirements
Step 1: Filter to clicked impressions
WHERE clicked = 1 restricts to ad impressions where the user actually clicked.
Step 2: Return distinct user IDs
SELECT DISTINCT user_id deduplicates users who clicked multiple ads.
The solution
SELECT DISTINCT user_id
FROM ad_impressions
WHERE clicked = 1Common follow-up questions
- How would you also return the number of clicks per user? (GROUP BY `user_id` with COUNT(*) instead of DISTINCT.)
- What if you needed usernames instead of user IDs? (JOIN to the users table on `user_id`.)
- How would you find users who saw ads but never clicked? (Anti-pattern: WHERE `user_id` NOT IN (SELECT `user_id` WHERE clicked = 1).)