Task
StreamWave wants to measure early user retention by signup cohort. Write a SQL query to calculate week-over-week retention for users who signed up in the last 90 days.
Requirements
- Treat each user's cohort week as the week of
signup_date, using DATE_TRUNC('week', signup_date).
- For each cohort week, count how many users were active in week 0, week 1, and week 2 after signup.
- Return the number of retained users and the retention rate for each cohort week and week number.
- Only include users whose
signup_date is within the last 90 days relative to DATE '2024-06-30'.
Table Definitions
users
| column | type | description |
|---|
| user_id | INT | Unique user identifier |
| signup_date | DATE | Date the user created an account |
| acquisition_channel | VARCHAR(50) | Marketing acquisition source |
user_activity
| column | type | description |
|---|
| activity_id | INT | Unique activity event identifier |
| user_id | INT | User who generated the activity |
| activity_date | DATE | Date of the activity |
| activity_type | VARCHAR(50) | Type of activity event |
Sample Data
users
| user_id | signup_date | acquisition_channel |
|---|
| 1 | 2024-04-02 | Organic |
| 2 | 2024-04-03 | Paid Search |
| 3 | 2024-04-10 | Referral |
| 4 | 2024-04-16 | Organic |
| 5 | 2024-05-01 | Social |
| 6 | 2024-05-08 | Email |
| 7 | 2024-03-20 | Paid Search |
| 8 | 2024-06-15 | Organic |
| 9 | 2024-02-20 | Referral |
| 10 | NULL | Social |
user_activity
| activity_id | user_id | activity_date | activity_type |
|---|
| 101 | 1 | 2024-04-03 | login |
| 102 | 1 | 2024-04-10 | stream |
| 103 | 2 | 2024-04-04 | login |
| 104 | 2 | 2024-04-17 | stream |
| 105 | 3 | 2024-04-11 | login |
| 106 | 3 | 2024-04-18 | stream |
| 107 | 4 | 2024-04-17 | login |
| 108 | 5 | 2024-05-02 | stream |
| 109 | 6 | 2024-05-15 | login |
| 110 | 8 | 2024-06-16 | login |
Expected Output
| cohort_week | week_number | cohort_size | retained_users | retention_rate |
|---|
| 2024-04-01 | 0 | 2 | 2 | 1.0000 |
| 2024-04-01 | 1 | 2 | 1 | 0.5000 |
| 2024-04-01 | 2 | 2 | 1 | 0.5000 |
| 2024-04-08 | 1 | 1 | 1 | 1.0000 |
| 2024-04-15 | 0 | 1 | 1 | 1.0000 |
| 2024-04-29 | 0 | 1 | 1 | 1.0000 |
| 2024-05-06 | 1 | 1 | 1 | 1.0000 |
| 2024-06-10 | 0 | 1 | 1 | 1.0000 |
