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.
Output details
- Return columns in this order:
cohort_month, change_period, cohort_size, retained_users_week_4, week_4_retention_rate.
- Sort
cohort_month in ascending chronological order.
- Treat signup dates before
2024-02-01 as pre_change and all other signup dates as post_change.
- Include event dates from signup date plus
21 through plus 27 days, inclusively.
- Count each retained user at most once, even if multiple qualifying events exist.
- Round
week_4_retention_rate to exactly 4 decimal places, with users having no qualifying event counted as zero retained.