Your question is Monthly Retention Cohort Analysis. Start with the requirements and the three 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 product activity data for a self-serve AI product. Write a PostgreSQL query that builds monthly signup cohorts and measures retention by month offset. A user belongs to the cohort of their signup month, and they are retained in month n if they had at least one qualifying activity in the nth calendar month after signup. Return one row per cohort month and month offset for offsets 0, 1, and 2, including cohort size, retained users, and retention rate.
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| signup_date | DATE | Date the user created their account |
| plan_type | VARCHAR(20) | Current subscription plan |
| acquisition_channel | VARCHAR(30) | Marketing or referral source |
| Column | Type | Description |
|---|---|---|
| membership_idPK | INT | Unique membership record |
| user_id | INT | User tied to the workspace |
| workspace_id | INT | Workspace identifier |
| joined_at | DATE | Date the user joined the workspace |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique event identifier |
| workspace_id | INT | Workspace where the event happened |
| event_date | DATE | Date of the usage event |
| event_name | VARCHAR(50) | Type of product event |
| event_count | INT | Number of events represented by the row |