Welcome to the SQL screen.
The question is on your right: Monthly Retention Cohort Analysis. Read through the requirements and the three tables first.
Run and submit your code as often as you need. You also have five interviewer messages this session - want to talk through your approach, or are you ready to start coding?
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 |