Your question is Six-Month 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 signup data and Nykaa app activity logs. Write a PostgreSQL query to calculate monthly cohort retention for the first six months after signup. Each cohort is defined by the user's signup month, and a user is retained in month n if they have at least one activity event in the nth month after their signup month. Return the cohort month, cohort size, retained users for months 0 through 5, and retention percentages for those months.
Use the signup month from users.created_at, count only users from the Nykaa App surface, and ignore activity rows where event_date is NULL.
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| created_at | DATE | User signup date |
| acquisition_channel | VARCHAR(50) | Marketing acquisition source |
| Column | Type | Description |
|---|---|---|
| activity_idPK | INT | Unique activity record |
| user_id | INT | User who generated the event |
| event_date | DATE | Date of activity |
| surface_name | VARCHAR(50) | Product surface where activity happened |