Your question is Monthly 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.
Bain & Company uses an internal analytics platform to monitor how consistently users return after their signup month. Write a PostgreSQL query that produces monthly retention cohorts from signup-month activity.
Assume a user belongs to a cohort based on their signup month. Month 0 is the signup month, and Months 1 through 3 are the following three calendar months. Count distinct users with activity in each period.
user_activity so one alias identifies signup-month activity and the second alias identifies subsequent activity.| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| user_name | VARCHAR(100) | User display name |
| signup_date | DATE | Date the user signed up |
| Column | Type | Description |
|---|---|---|
| activity_idPK | INT | Unique activity record |
| user_id | INT | User associated with the activity |
| activity_date | DATE | Date of platform activity |