D8LooPFocus modeCODE WORKSPACE
Interview cohort retention slice
Return cohort_day, day_offset, and active_buyers using first order day and later activity days.
Start by naming the output grain, then build CTEs that can be inspected one at a time.
Sample input
ordersCohort day is each buyer’s first order date; activity day is any later order date.
| buyer_id | created_at |
|---|---|
| 1 | 2026-01-12 08:40:00 |
| 1 | 2026-01-15 14:20:00 |
| 3 | 2026-01-12 10:02:00 |
| 3 | 2026-01-22 14:46:00 |
Expected output
Expected outputFirst rows of the cohort slice; day_offset 0 is the first order day.
| cohort_day | day_offset | active_buyers |
|---|---|---|
| 2026-01-12 | 0 | 2 |
| 2026-01-12 | 3 | 1 |
| 2026-01-12 | 10 | 1 |
Constraints
State the output contract first, use readable CTE layers, return the exact columns requested by the prompt, and use deterministic ordering.
Expected skills
Interview clarification, SQL structure, edge-case handling, and final-answer narration.
SQL
Loading...
AI evaluation
Run the SQL query to inspect preview rows.