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.
US region.view_workout event in January 2024.start_workout event after that first view_workout.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 |
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 |
| users_viewed | users_started | users_dropped_off | dropoff_rate_pct |
|---|---|---|---|
| 5 | 3 | 2 | 40.00 |
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| user_name | VARCHAR(100) | User full name |
| region | VARCHAR(20) | User region |
| signup_date | DATE | Date the user signed up |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique event identifier |
| user_id | INT | User who triggered the event |
| event_name | VARCHAR(50) | Name of the in-app event |
| event_time | TIMESTAMP | Timestamp when the event occurred |
| device_type | VARCHAR(20) | Device used for the event |
{"users":[["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"],["9","Ivan Petrov",null,"2024-01-15"],["10","Lina Ahmed","NZ","2023-12-01"],["11","Ben Miller","DK","2023-12-25"],["12","Tina Wu","KR","2023-12-29"],["13","Ben Miller","UK","2023-12-30"],["14","NinaOutput[["5","3","2","40.00"]]
