Your question is Compare Asana Retention Cohorts by Channel. 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.
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.
users.signup_date.ChatGPT when acquisition_channel = 'ChatGPT'Other for every other non-null channel
Exclude users with a null acquisition channel.n if they had at least one activity in Asana during the nth week after signup, using activity_date from user_activity.cohort_week, acquisition_segment, and week_number for weeks 0-4.cohort_size, retained_users, and retention_rate rounded to 4 decimal places.cohort_week, acquisition_segment, and week_number.| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique Asana user identifier |
| signup_date | DATE | Date the user signed up |
| acquisition_channel | VARCHAR(50) | Marketing or referral source that acquired the user |
| workspace_id | INT | First Asana workspace the user joined |
| Column | Type | Description |
|---|---|---|
| activity_idPK | INT | Unique activity event identifier |
| user_id | INT | User who performed the activity |
| activity_date | DATE | Date of the Asana activity |
| activity_type | VARCHAR(50) | Type of activity performed |