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.
Slack integration.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 |
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 |
| total_slack_signups | retained_users_day_7 | retention_rate_pct |
|---|---|---|
| 5 | 2 | 40.00 |
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| signup_ts | TIMESTAMP | Timestamp when the user signed up for Asana |
| workspace_id | INT | Workspace joined at signup |
| Column | Type | Description |
|---|---|---|
| signup_idPK | INT | Unique integration signup record |
| user_id | INT | User associated with the integration signup |
| integration_name | VARCHAR(50) | Integration credited for acquisition |
| connected_ts | TIMESTAMP | Timestamp when the integration was connected |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique event identifier |
| user_id | INT | User who performed the event |
| event_name | VARCHAR(50) | Name of the user activity event |
| event_ts | TIMESTAMP | Timestamp when the event occurred |
{"users":[["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"],["11","null","102"],["12","2024-01-02 15:08:26","100"],["13","2023-12-30 17:33:28","103"],["14","2024-01-04 18:02:28","105"],["15","2024-01-06 17:18:50","108"],["16",Output[["5","5","100.00"]]