SQL interview question: day-1 retention by channel
Problem
Shared data rulesFor each acquisition channel, find the next-day return rate of users who signed up Jul 6–Sep 26, 2026. Came back = a session_start on the calendar date after the signup date.
Formula
d1_retention = returned_d1 / signups
Example
users| user_id | signup_ts | acq_channel |
|---|---|---|
| 1 | 2026-08-10 22:30 | ads |
| 2 | 2026-08-20 10:00 | ads |
| 3 | 2026-08-12 09:00 | organic |
| 4 | 2026-09-01 11:00 | organic |
events| user_id | event_ts | event_name | Row note |
|---|---|---|---|
| 1 | 2026-08-11 08:00 | session_start | ← 9.5 h later — already next date |
| 2 | 2026-08-22 15:00 | session_start | ← two days later — doesn’t count |
| 3 | 2026-08-12 18:00 | session_start | ← signup day — doesn’t count |
| 3 | 2026-08-13 20:00 | session_start | |
| 4 | 2026-09-02 10:00 | session_start | |
| 4 | 2026-09-02 10:00 | session_start | ← duplicate — still one return |
- Row 1: 9.5 h later — already next date
- Row 2: two days later — doesn’t count
- Row 3: signup day — doesn’t count
- Row 6: duplicate — still one return
| acq_channel | signups | returned_d1 | d1_retention |
|---|---|---|---|
| ads | 2 | 1 | 0.5 |
| organic | 2 | 2 | 1 |
ads: 1 of 2 came back — 0.5; organic: 2 of 2 — 1.
Solve it free
Your query runs on PostgreSQL in your browser. No sign-up needed.
Skills it tests
More interview questions
- SQL: Second-highest price in each groupEasy · Nth-highest value
- SQL: Top 3 categories in each cityMedium · Top-N per group
- SQL: Find the bugs in a churn queryMedium · Code review
- SQL: Find the bugs in a revenue queryMedium · Code review
- Pandas: Find the bugs in an average order value notebookEasy · Code review