Task
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 |
Sample Data
users
| user_id | signup_date | country |
|---|
| 5 | 2024-04-20 | US |
| 1 | 2024-02-10 | US |
| 8 | 2024-05-18 | BR |
cohort_table
| user_id | cohort_month |
|---|
| 1 | 2024-02-01 |
| 5 | 2024-04-01 |
| 8 | 2024-05-01 |
user_activity
| activity_id | user_id | activity_date | activity_type |
|---|
| 104 | 1 | 2024-03-05 | session |
| 111 | 5 | 2024-05-05 | purchase |
| 116 | 8 | 2024-06-02 | session |
Expected Output
| cohort_month | launch_period | cohort_size | retained_users | retention_rate |
|---|
| 2024-02-01 | pre_launch | 2 | 1 | 50.00 |
| 2024-03-01 | pre_launch | 2 | 1 | 50.00 |
| 2024-04-01 | post_launch | 3 | 2 | 66.67 |
| 2024-05-01 | post_launch | 2 | 1 | 50.00 |
