Your question is Compare Monthly User Retention Cohorts. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
You are given user signups and activity events. Write a PostgreSQL query that calculates month-1 retention for users who signed up in January versus February. Return one row per signup month with the cohort size, retained users, and retention rate.
Count a user as retained only if they had at least one activity event in the next calendar month after signup. Use the signup month to define the cohort and do not hardcode the retention denominator.
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Primary key for each user |
| signup_date | DATE | Date the user signed up |
| country | VARCHAR(50) | User country |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Primary key for each activity event |
| user_id | INT | Foreign key to users.user_id |
| event_date | DATE | Date of the activity event |
| event_type | VARCHAR(50) | Type of activity event |