NovaPlay launched a major product update on 2024-04-01 and wants to understand whether user retention changed after the launch. Write a PostgreSQL query that uses a cohort table to compare retention for pre-launch and post-launch signup cohorts.
Requirements
- Treat each user's cohort as the month of their
signup_date from the cohort_table.
- For each cohort month, calculate month 1 retention: the percentage of users who had at least one activity event in the calendar month immediately after their cohort month.
- Label each cohort as
pre_launch or post_launch based on whether the cohort month is before 2024-04-01.
- Return one row per cohort month with cohort size, retained users, and retention rate rounded to 2 decimals.
Table Definitions
users
| column | type | description |
|---|
| user_id | INT | Unique user identifier |
| signup_date | DATE | User signup date |
| country | VARCHAR(50) | User country |
cohort_table
| column | type | description |
|---|
| user_id | INT | User identifier |
| cohort_month | DATE | First day of the user's signup month |
user_activity
| column | type | description |
|---|
| activity_id | INT | Unique activity event identifier |
| user_id | INT | User identifier |
| activity_date | DATE | Date of activity |
| activity_type | VARCHAR(50) | Type of activity |
