Task
PulseFit wants to measure how many users drop off between two in-app events: view_workout and start_workout. Write a SQL query to calculate the drop-off rate for users who triggered the first event during January 2024.
Use the users table for user attributes and the app_events table for event activity. A user should count as having completed the funnel only if they performed start_workout after their first view_workout in the month.
Requirements
- Consider only users in the
US region.
- Find each user's first
view_workout event in January 2024.
- Determine whether that user had at least one
start_workout event after that first view_workout.
- Return the total number of users at step 1, the number who reached step 2, the number who dropped off, and the drop-off rate as a percentage rounded to 2 decimals.
Table Definitions
users
| column | type | description |
|---|
| user_id | INT | Unique user identifier |
| user_name | VARCHAR(100) | User name |
| region | VARCHAR(20) | User region |
| signup_date | DATE | Account creation date |
app_events
| column | type | description |
|---|
| event_id | INT | Unique event identifier |
| user_id | INT | User who triggered the event |
| event_name | VARCHAR(50) | In-app event name |
| event_time | TIMESTAMP | Event timestamp |
| device_type | VARCHAR(20) | Device used for the event |
Sample Data
users
| user_id | user_name | region | signup_date |
|---|
| 3 | Carla Diaz | US | 2023-12-20 |
| 1 | Alice Chen | US | 2023-11-15 |
| 6 | Farah Ali | US | 2024-01-05 |
| 2 | Ben Miller | CA | 2023-12-01 |
| 5 | Ethan Ross | US | 2024-01-03 |
| 4 | Diego Park | UK | 2024-01-10 |
| 8 | Hana Kim | US | 2024-01-12 |
| 7 | Grace Liu | US | 2024-01-08 |
app_events
| event_id | user_id | event_name | event_time | device_type |
|---|
| 101 | 1 | app_open | 2024-01-02 08:00:00 | iOS |
| 102 | 1 | view_workout | 2024-01-02 08:05:00 | iOS |
| 103 | 1 | start_workout | 2024-01-02 08:10:00 | iOS |
| 104 | 2 | view_workout | 2024-01-03 09:00:00 | Android |
| 105 | 2 | start_workout | 2024-01-03 09:05:00 | Android |
| 106 | 3 | view_workout | 2024-01-04 10:00:00 | iOS |
| 107 | 3 | app_open | 2024-01-04 10:02:00 | iOS |
| 108 | 4 | view_workout | 2024-01-05 11:00:00 | Android |
| 109 | 5 | start_workout | 2024-01-06 07:55:00 | Web |
| 110 | 5 | view_workout | 2024-01-06 08:00:00 | Web |
| 111 | 6 | view_workout | 2024-01-07 12:00:00 | null |
| 112 | 6 | start_workout | 2024-01-07 12:20:00 | Android |
| 113 | 7 | view_workout | 2023-12-31 23:50:00 | iOS |
| 114 | 7 | start_workout | 2024-01-01 00:10:00 | iOS |
| 115 | 8 | view_workout | 2024-01-10 14:00:00 | Android |
| 116 | 8 | view_workout | 2024-01-10 14:05:00 | Android |
| 117 | 8 | start_workout | 2024-01-10 14:06:00 | Android |
Expected Output
| users_viewed | users_started | users_dropped_off | dropoff_rate_pct |
|---|
| 5 | 3 | 2 | 40.00 |
