Task
Asana’s growth team wants to compare early retention for users acquired through ChatGPT versus all other acquisition channels. Write a PostgreSQL query that builds weekly signup cohorts and reports week 0 through week 4 retention for each cohort and acquisition segment.
Requirements
- Define each user’s cohort by the week of signup from
users.signup_date.
- Split users into two acquisition segments:
ChatGPT when acquisition_channel = 'ChatGPT'Other for every other non-null channel
Exclude users with a null acquisition channel.
- Count a user as retained in week
n if they had at least one activity in Asana during the nth week after signup, using activity_date from user_activity.
- Return one row per
cohort_week, acquisition_segment, and week_number for weeks 0-4.
- Include
cohort_size, retained_users, and retention_rate rounded to 4 decimal places.
- Order the final output by
cohort_week, acquisition_segment, and week_number.
Table Definitions
users
| column | type | description |
|---|
| user_id | INT | Unique user id |
| signup_date | DATE | Date the user signed up for Asana |
| acquisition_channel | VARCHAR(50) | Signup acquisition source |
| workspace_id | INT | First workspace joined |
user_activity
| column | type | description |
|---|
| activity_id | INT | Unique activity event id |
| user_id | INT | User who performed the activity |
| activity_date | DATE | Date of the activity |
| activity_type | VARCHAR(50) | Activity performed in Asana |
Sample Data
users
| user_id | signup_date | acquisition_channel | workspace_id |
|---|
| 3 | 2024-01-10 | Paid Search | 101 |
| 1 | 2024-01-02 | ChatGPT | 101 |
| 8 | 2024-01-17 | Referral | 105 |
| 5 | 2024-01-09 | ChatGPT | 103 |
user_activity
| activity_id | user_id | activity_date | activity_type |
|---|
| 104 | 1 | 2024-01-15 | task_completed |
| 101 | 1 | 2024-01-03 | task_created |
| 112 | 5 | 2024-01-23 | comment_added |
| 118 | 8 | 2024-02-15 | task_created |
Expected Output
| cohort_week | acquisition_segment | week_number | cohort_size | retained_users | retention_rate |
|---|
| 2024-01-01 | ChatGPT | 0 | 2 | 2 | 1.0000 |
| 2024-01-01 | ChatGPT | 1 | 2 | 1 | 0.5000 |
| 2024-01-01 | Other | 0 | 2 | 1 | 0.5000 |
| 2024-01-15 | Other | 4 | 1 | 1 | 1.0000 |
