D8LooPFocus modeCODE WORKSPACE
Session retention breakdown
Write a query that returns weekly retention by signup cohort for active users in the first eight weeks after signup.
Start by isolating the first valid signup week per user, then layer active weeks after deduplication.
Sample input
user_eventsSample activity rows from the sandbox dataset.
| user_id | signup_week | active_week | event_name |
|---|---|---|---|
| 1 | 1 | 1 | signup |
| 1 | 1 | 2 | session_start |
| 2 | 1 | 1 | signup |
| 3 | 2 | 2 | signup |
Expected output
Expected output shapeExample shape only. Your final query should produce the full retention breakdown from the sandbox.
| signup_week | active_week | retained_users |
|---|---|---|
| 1 | 1 | 2 |
| 1 | 2 | 1 |
| 2 | 2 | 1 |
Constraints
Assume duplicate events exist, users can reactivate, and the final answer must be readable enough for an interviewer to follow.
Expected skills
Window functions, cohort logic, deduplication, and communicating tradeoffs.
SQL
Loading...
AI evaluation
Run the SQL query to inspect preview rows.