Task
Asana’s growth team wants to measure how many new users activate quickly after signup. Write a SQL query to identify users who activated within 24 hours of signup.
Requirements
- Return each
user_id whose activated_at timestamp is within 24 hours after signup_at.
- Exclude users who never activated or whose activation happened before signup.
- Include
signup_at, activated_at, and the activation lag in hours.
- Order the result by
user_id.
Table Definition
asana_user_lifecycle
| column | type | description |
|---|
| user_id | INT | Unique user identifier |
| email | VARCHAR(255) | User email address |
| signup_at | TIMESTAMP | Time the user created an Asana account |
| activated_at | TIMESTAMP | Time the user completed the activation event in Asana |
| signup_surface | VARCHAR(100) | Surface where the signup started |
Sample Data
Expected Output
| user_id | signup_at | activated_at | activation_hours |
|---|
| 101 | 2024-04-01 08:00:00 | 2024-04-01 12:00:00 | 4.00 |
| 104 | 2024-04-03 09:00:00 | 2024-04-04 09:00:00 | 24.00 |
| 108 | 2024-04-05 14:00:00 | 2024-04-06 13:59:00 | 23.98 |
