Your question is Invite Behavior and User Retention. Start with the requirements and the three 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.
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.
| 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 |