Task
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.
Schema
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 |
Sample data
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
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 |
| | | |
Expected output
| invite_cohort | total_users | retained_users | retention_rate |
|---|
| invited_teammate | 4 | 3 | 0.7500 |
| did_not_invite | 6 | 2 | 0.3333 |
