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.
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| signup_date | DATE | Date the user signed up |
| cohort_month | DATE | First day of the user's signup month |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique event identifier |
| user_id | INT | User who generated the event |
| event_date | DATE | Date of the event |
| surface_name | VARCHAR(50) | Quora surface where the event occurred |
You are practicing as a guest. Sign up free to run your code against the sample data. Your draft stays right here.
