Task
You are given user signup cohorts and Quora activity events around a product change to the Quora Home Feed launched on 2024-02-01. Write a PostgreSQL query that uses the cohort table to compare retention for users who signed up before vs. after the change. For each cohort month, return the number of users in the cohort, the number of users retained in week 4 (days 21-27 after signup), and the week-4 retention rate. Label each cohort as pre_change or post_change based on the signup date, and order the results chronologically.
Schema
user_cohorts
| column | type | description |
|---|
| user_id | INT | User identifier |
| signup_date | DATE | User signup date |
| cohort_month | DATE | First day of the signup month |
| | |
quora_events
| column | type | description |
|---|
| event_id | INT | Event identifier |
| user_id | INT | User who generated the event |
| event_date | DATE | Date of activity |
| surface_name | VARCHAR(50) | Quora surface where activity happened |
| | |
Sample data
user_cohorts
| user_id | signup_date | cohort_month |
|---|
| 103 | 2024-01-10 | 2024-01-01 |
| 101 | 2024-01-28 | 2024-01-01 |
| 106 | 2024-02-03 | 2024-02-01 |
| | |
quora_events
| event_id | user_id | event_date | surface_name |
|---|
| 2008 | 103 | 2024-02-01 | Home Feed |
| 2002 | 101 | 2024-02-20 | Home Feed |
| 2011 | 106 | 2024-03-01 | Home Feed |
| | | |
Expected output
| cohort_month | change_period | cohort_size | retained_users_week_4 | week_4_retention_rate |
|---|
| 2024-01-01 | pre_change | 5 | 2 | 0.4000 |
| 2024-02-01 | post_change | 4 | 3 | 0.7500 |
