SQL interview question: 30-day customer lifetime value
Problem
Shared data rulesFor each acquisition channel, find LTV-30: average revenue per user over the first 30 days after signup. Count only users whose 30 days fit entirely in the data.
Formulas
- a user’s 30 days:
signup_ts <= created_at < signup_ts + 30 days ltv_30d_usd = revenue_usd / cohort_users
Example
users| user_id | signup_ts | acq_channel | Row note |
|---|---|---|---|
| 1 | 2026-08-01 13:00 | ads | |
| 2 | 2026-08-10 12:00 | ads | ← no orders — still in the denominator |
| 3 | 2026-09-10 15:00 | ads | ← 30 days don’t fit |
| 4 | 2026-07-20 11:00 | organic | |
| 5 | 2026-07-01 11:00 | organic | ← before Jul 6 — doesn’t fit |
- User 2: no orders — still in the denominator
- User 3: 30 days don’t fit
- User 5: before Jul 6 — doesn’t fit
orders| order_id | user_id | created_at | status | amount_usd | Row note |
|---|---|---|---|---|---|
| 201 | 1 | 2026-08-05 15:00 | delivered | 30 | |
| 202 | 1 | 2026-08-31 12:00 | delivered | 18 | ← 29 d 23 h after signup |
| 203 | 1 | 2026-08-31 14:00 | delivered | 14 | ← 30 d 1 h — doesn’t count |
| 204 | 3 | 2026-09-12 11:00 | delivered | 12 | |
| 205 | 4 | 2026-07-25 18:00 | delivered | 40 |
- Order 202: 29 d 23 h after signup
- Order 203: 30 d 1 h — doesn’t count
| acq_channel | cohort_users | buyers | revenue_usd | ltv_30d_usd |
|---|---|---|---|---|
| ads | 2 | 1 | 48 | 24 |
| organic | 1 | 1 | 40 | 40 |
ads: (30 + 18) / 2 = $24 per cohort user; organic: 40 / 1 = $40.
Output columns
buyers- cohort users with an order within their 30 days
Notes
- Orders in the database start on Jul 6, 2026.
Solve it free
Your query runs on PostgreSQL in your browser. No sign-up needed.