You are given the task of measuring whether users who invite teammates in Asana are more likely to retain. Write a SQL query that groups users by whether they sent at least one teammate invite within 7 days of signup, then calculates each group’s 30-day retention rate. Treat a user as retained if they have at least one activity event on or after day 30 and on or before day 60 after signup. Return the invite cohort, total users, retained users, and retention rate, ordered by the invite cohort.
users| column | type | description |
|---|---|---|
| user_id | INT | Unique user ID |
| signup_date | DATE | Date the user created their account |
| plan_tier | VARCHAR(20) | User plan tier at signup |
| country_code | VARCHAR(2) | User country |
invites| column | type | description |
|---|---|---|
| invite_id | INT | Unique invite ID |
| inviter_user_id | INT | User who sent the invite |
| invitee_email | VARCHAR(255) | Email invited to join |
| sent_at | DATE | Invite send date |
activity_events| column | type | description |
|---|---|---|
| event_id | INT | Unique activity event ID |
| user_id | INT | User performing the action |
| event_date | DATE | Date of activity |
| event_name | VARCHAR(50) | Activity type |
users| user_id | signup_date | plan_tier | country_code |
|---|---|---|---|
| 101 | 2024-01-01 | Free | US |
| 102 | 2024-01-02 | Starter | US |
| 103 | 2024-01-05 | Free | CA |
invites| invite_id | inviter_user_id | invitee_email | sent_at |
|---|---|---|---|
| 201 | 101 | alex@team.com | 2024-01-03 |
| 202 | 103 | sam@team.com | 2024-01-20 |
| 203 | 102 | pat@team.com | 2024-01-08 |
activity_events| event_id | user_id | event_date | event_name |
|---|---|---|---|
| 301 | 101 | 2024-02-05 | task_completed |
| 302 | 102 | 2024-01-20 | project_viewed |
| 303 | 103 | 2024-02-10 | comment_added |
| invite_cohort | total_users | retained_users | retention_rate |
|---|---|---|---|
| invited_teammate | 4 | 3 | 0.7500 |
| did_not_invite | 6 | 2 | 0.3333 |
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| signup_date | DATE | Date the user signed up for Asana |
| plan_tier | VARCHAR(20) | Plan tier at signup |
| country_code | VARCHAR(2) | Two-letter country code |
| Column | Type | Description |
|---|---|---|
| invite_idPK | INT | Unique invite identifier |
| inviter_user_id | INT | User who sent the teammate invite |
| invitee_email | VARCHAR(255) | Email address invited to join |
| sent_at | DATE | Date the invite was sent |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique event identifier |
| user_id | INT | User who generated the activity event |
| event_date | DATE | Date of the activity event |
| event_name | VARCHAR(50) | Type of activity event in Asana |
{"users":[[101,"2024-01-01","Free","US"],[102,"2024-01-02","Starter","US"],[103,"2024-01-05","Free","CA"],[104,"2024-01-10","Advanced","GB"],[105,"2024-01-12","Free","IN"],[106,"2024-01-15","Starter","US"],[107,"2024-01-20",null,"DE"],[108,"2024-01-22","Free",null],[109,"2024-01-25","Enterprise","US"],[110,"2024-01-28","Starter","AU"],["1","2024-01-11","Partner","NZ"],["2","2024-01-26","Enterprise","MX"],["3","2024-01-20","Free","AE"],["4","2024-01-20","Personal","IE"],["5","2024-01-24","NonprofOutput[["invited_teammate","5","3","0.6000"],["did_not_invite","25","6","0.2400"]]
