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.
signup_date, using DATE_TRUNC('week', signup_date).signup_date is within the last 90 days relative to DATE '2024-06-30'.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 |
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 | |
| 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 |
| 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 |
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| signup_date | DATE | Date the user signed up |
| acquisition_channel | VARCHAR(50) | Marketing channel that acquired the user |
| Column | Type | Description |
|---|---|---|
| activity_idPK | INT | Unique activity event identifier |
| user_id | INT | User who performed the activity |
| activity_date | DATE | Date of the activity |
| activity_type | VARCHAR(50) | Type of user activity |
{"users":[[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"],["1","2024-02-17","Referral"],["2","2024-06-09","SEO"],["3","2024-03-01","LinkedIn"],["4","2024-02-23","YouTube"],["5","2024-03-17","Facebook Ads"],["6","null","Facebook Ads"],["7","2024-04-11","Instagram Ads"],["8","2024Output[["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","0","1","1","1.0000"],["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","2","1","0.5000"],["2024-06-10","0","2","1","0.5000"]]
