Task
Asana’s growth team wants to measure whether users acquired through a specific third-party integration come back one week after signup. Write a SQL query to calculate the 7-day retention rate for users who signed up through the Slack integration.
Requirements
- Identify users whose signup source is the
Slack integration.
- Define a retained user as someone who has at least one activity event on the exact date that is 7 days after their signup date.
- Return the total number of Slack-signup users, the number retained on day 7, and the retention rate as a percentage rounded to 2 decimals.
- Exclude users with a NULL signup timestamp or NULL integration source.
Table Definitions
users
| column | type | description |
|---|
| user_id | INT | Unique user identifier |
| signup_ts | TIMESTAMP | When the user created their Asana account |
| workspace_id | INT | Workspace the user joined at signup |
| | |
integration_signups
| column | type | description |
|---|
| signup_id | INT | Unique integration signup record |
| user_id | INT | User tied to the integration signup |
| integration_name | VARCHAR(50) | Integration credited for signup |
| connected_ts | TIMESTAMP | When the integration was connected |
| | |
user_events
| column | type | description |
|---|
| event_id | INT | Unique event identifier |
| user_id | INT | User who performed the event |
| event_name | VARCHAR(50) | Activity performed in Asana |
| event_ts | TIMESTAMP | Event timestamp |
Sample Data
users
| user_id | signup_ts | workspace_id |
|---|
| 3 | 2024-01-03 11:00:00 | 102 |
| 1 | 2024-01-01 09:00:00 | 101 |
| 8 | 2024-01-08 10:00:00 | 107 |
| 5 | 2024-01-05 12:00:00 | 104 |
| 2 | 2024-01-02 10:00:00 | 101 |
| 10 | NULL | 109 |
| 6 | 2024-01-06 13:00:00 | 105 |
| 4 | 2024-01-04 08:30:00 | 103 |
| 7 | 2024-01-07 09:15:00 | 106 |
| 9 | 2024-01-09 14:00:00 | 108 |
integration_signups
| signup_id | user_id | integration_name | connected_ts |
|---|
| 201 | 1 | Slack | 2024-01-01 09:05:00 |
| 202 | 2 | Slack | 2024-01-02 10:05:00 |
| 203 | 3 | Google Calendar | 2024-01-03 11:10:00 |
| 204 | 4 | Slack | 2024-01-04 08:35:00 |
| 205 | 5 | Microsoft Teams | 2024-01-05 12:05:00 |
| 206 | 6 | Slack | 2024-01-06 13:05:00 |
| 207 | 7 | NULL | 2024-01-07 09:20:00 |
| 208 | 8 | Slack | 2024-01-08 10:05:00 |
| 209 | 9 | Zoom | 2024-01-09 14:05:00 |
| 210 | 11 | Slack | 2024-01-10 09:00:00 |
user_events
| event_id | user_id | event_name | event_ts |
|---|
| 308 | 8 | task_viewed | 2024-01-14 10:00:00 |
| 301 | 1 | task_created | 2024-01-08 08:00:00 |
| 309 | 8 | task_created | 2024-01-15 11:00:00 |
| 302 | 1 | comment_added | 2024-01-09 09:00:00 |
| 303 | 2 | project_viewed | 2024-01-09 12:00:00 |
| 304 | 2 | task_completed | 2024-01-10 15:00:00 |
| 305 | 4 | login | 2024-01-10 07:00:00 |
| 306 | 4 | task_created | 2024-01-11 09:00:00 |
| 307 | 6 | project_created | 2024-01-13 16:00:00 |
| 310 | 12 | login | 2024-01-08 09:00:00 |
Expected Output
| total_slack_signups | retained_users_day_7 | retention_rate_pct |
|---|
| 5 | 2 | 40.00 |
