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 |