Task
You are given event logs from a fleet workflow product and need to build a user-level event sequence for each driver in the Motive Driver App. Write a query that returns, for every non-null driver_id, each event in chronological order along with its sequence number, the previous event name and timestamp, the next event name and timestamp, and the minutes since the previous event. If two events have the same timestamp for a driver, break ties with event_id. Exclude rows where driver_id is null.
Schema
| column | type | description |
|---|
| event_id | INT | Unique event record ID |
| driver_id | INT | Driver identifier |
| event_name | VARCHAR(50) | Event captured in the Motive Driver App |
| event_ts | TIMESTAMP | Event timestamp |
| surface | VARCHAR(50) | Product surface where the event occurred |
| trip_id | INT | Associated trip, if any |
| | |
Sample data
| event_id | driver_id | event_name | event_ts | surface | trip_id |
|---|
| 102 | 101 | login | 2024-03-01 08:00:00 | Driver App | null |
| 101 | 101 | view_dispatch | 2024-03-01 08:00:00 | Driver App | 5001 |
| 103 | 101 | accept_load | 2024-03-01 08:03:00 | Driver App | 5001 |
| 111 | 104 | login | 2024-03-01 07:50:00 | Driver App | null |
| 110 | null | login | 2024-03-01 07:00:00 | Driver App | null |
| | | | | |
Expected output
| driver_id | event_id | event_name | event_ts | event_sequence_num | previous_event_name | previous_event_ts | next_event_name | next_event_ts | minutes_since_previous |
|---|
| 101 | 101 | view_dispatch | 2024-03-01 08:00:00 | 1 | null | null | login | 2024-03-01 08:00:00 | null |
| 101 | 102 | login | 2024-03-01 08:00:00 | 2 | view_dispatch | 2024-03-01 08:00:00 | accept_load | 2024-03-01 08:03:00 | 0 |
| 101 | 103 | accept_load | 2024-03-01 08:03:00 | 3 | login | 2024-03-01 08:00:00 | start_trip | 2024-03-01 08:10:00 | 3 |
