Clicked Ad Impressions
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 analytics team needs the raw data behind every clicked impression for a detailed review. Pull all fields from ad impression records where the user clicked.
They saw the ad. They clicked.
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 |
Expected output
| impression_id | user_id | ad_campaign | impression_time | clicked | revenue |
|---|---|---|---|---|---|
| 465 | 488 | NEW_USER_ACQU | 2026-06-06 05:35:00 | 1 | 0.2 |
| 730 | 973 | PRODUCT_LAUNCH_X | 2026-02-11 10:10:00 | 1 | 0.35 |
| 995 | 488 | FLASH_SALE_48H | 2026-07-16 15:45:00 | 1 | 0.5 |
| 1260 | 973 | HOLIDAY_PROMO | 2026-03-21 20:20:00 | 1 | 0.65 |
| 1525 | 488 | BRAND_AWARENESS_Q1 | 2026-08-26 01:55:00 | 1 | 0.8 |
Worked solution and explanation
Why this problem exists in real interviews
This is the simplest possible filter query. It screens for the baseline ability to write a WHERE clause and use SELECT * correctly.
Break down the requirements
Step 1: Filter to clicked impressions
WHERE clicked = 1 restricts to rows where the user clicked.
Step 2: Return all fields
SELECT * returns every column as requested.
The solution
SELECT *
FROM ad_impressions
WHERE clicked = 1Common follow-up questions
- Why might SELECT * be problematic in production? (Tests awareness of schema evolution, network bandwidth, and query plan stability.)
- How would you index this table for fast clicked-only queries? (Tests partial index knowledge: CREATE INDEX ON `ad_impressions` (`impression_id`) WHERE clicked = 1.)
- What if clicked were a boolean column? (Tests type-aware filtering: WHERE clicked = true vs WHERE clicked = 1.)