SQL interview question: weekly cohort retention table
Problem
Shared data rulesBuild a weekly cohort retention table for signup weeks starting Jul 6 through Aug 31, 2026. For each cohort, find the share who bought in each of weeks 1–4 after the signup week.
Formula
retention = active_users / cohort_size
Example
users| user_id | signup_ts | Row note |
|---|---|---|
| 1 | 2026-08-31 10:00 | |
| 2 | 2026-09-02 13:00 | |
| 3 | 2026-09-03 13:00 | ← never bought |
- User 3: never bought
orders| order_id | user_id | created_at | status | Row note |
|---|---|---|---|---|
| 101 | 1 | 2026-09-08 15:00 | delivered | ← week 1 |
| 102 | 2 | 2026-09-03 18:00 | delivered | ← week 0 — not in the table |
| 103 | 2 | 2026-09-10 13:00 | delivered | ← week 1 |
| 104 | 1 | 2026-09-21 11:00 | delivered | ← week 3 |
| 105 | 2 | 2026-09-28 01:30 | delivered | ← week 4 — not over yet |
- Order 101: week 1
- Order 102: week 0 — not in the table
- Order 103: week 1
- Order 104: week 3
- Order 105: week 4 — not over yet
| cohort_week | week_num | cohort_size | active_users | retention |
|---|---|---|---|---|
| 2026-08-31 | 1 | 3 | 2 | 0.6667 |
| 2026-08-31 | 2 | 3 | 0 | 0 |
| 2026-08-31 | 3 | 3 | 1 | 0.3333 |
The Aug 31 cohort has 3 users. Week 2 has no purchases but stays as a zero row, and week 4 (Sep 28 – Oct 4) hasn’t ended in the data, so it has no row.
Output columns
week_num- 1–4: weeks after the signup week (that one is week 0)
cohort_size- all cohort users, including those who never bought
Solve it free
Your query runs on PostgreSQL in your browser. No sign-up needed.