Business Context
You’re working on the data platform for a self-driving vehicle fleet operating in multiple US cities. The fleet generates a high-volume event stream (billions of rows/month) where each event records a vehicle’s current control state (e.g., autonomous vs manual). Safety and compliance teams use these logs to audit disengagements and to report how much time vehicles spend under autonomous control versus human control.
A common issue is that the event stream is state-change oriented: you only get a new row when the state is recorded (often on change, but sometimes periodically). To compute time-in-state, you must interpret each row as the start of an interval that ends at the next timestamp for the same vehicle.
Task
Write a SQL query that calculates, for each vehicle and calendar day, the total time (in seconds) spent in autonomous mode versus manual mode.
Requirements
- Consider each row in
vehicle_state_events as the start of a state interval.
- The interval ends at the next event timestamp for the same vehicle.
- If there is no next event for the vehicle, end the interval at
trip_end_ts from vehicle_trips.
- Split time by vehicle_id + trip_date (derived from
trip_start_ts), and return two columns: autonomous_seconds and manual_seconds.
- Ignore any intervals where the computed end time is <= start time (bad data / duplicates).
- Output should include one row per
vehicle_id, trip_date.
Table Definitions
vehicle_trips
| Column | Type | Description |
|---|
| trip_id | BIGINT | Unique trip identifier |
| vehicle_id | BIGINT | Vehicle identifier |
| trip_start_ts | TIMESTAMP | Trip start timestamp (UTC) |
| trip_end_ts | TIMESTAMP | Trip end timestamp (UTC) |
vehicle_state_events
| Column | Type | Description |
|---|
| event_id | BIGINT | Unique event identifier |
| trip_id | BIGINT | Trip identifier referencing vehicle_trips |
| vehicle_id | BIGINT | Vehicle identifier (denormalized for performance) |
| event_ts | TIMESTAMP | Timestamp when this state became active (UTC) |
| vehicle_state | VARCHAR(32) | Control state: autonomous or manual |
Sample Data
vehicle_trips
| trip_id | vehicle_id | trip_start_ts | trip_end_ts |
|---|
| 9001 | 101 | 2026-01-15 08:00:00 | 2026-01-15 08:30:00 |
| 9002 | 102 | 2026-01-15 09:00:00 | 2026-01-15 09:20:00 |
| 9003 | 101 | 2026-01-16 10:00:00 | 2026-01-16 10:10:00 |
vehicle_state_events
| event_id | trip_id | vehicle_id | event_ts | vehicle_state |
|---|
| 1 | 9001 | 101 | 2026-01-15 08:00:00 | autonomous |
| 2 | 9001 | 101 | 2026-01-15 08:10:00 | manual |
| 3 | 9001 | 101 | 2026-01-15 08:25:00 | autonomous |
| 4 | 9002 | 102 | 2026-01-15 09:00:00 | manual |
| 5 | 9002 | 102 | 2026-01-15 09:05:00 | autonomous |
| 6 | 9003 | 101 | 2026-01-16 10:00:00 | autonomous |
Expected Output (for sample data)
| vehicle_id | trip_date | autonomous_seconds | manual_seconds |
|---|
| 101 | 2026-01-15 | 900 | 900 |
| 101 | 2026-01-16 | 600 | 0 |
| 102 | 2026-01-15 | 900 | 300 |
Explanation of sample (vehicle 101, 2026-01-15):
- 08:00→08:10 autonomous = 600s
- 08:10→08:25 manual = 900s
- 08:25→08:30 autonomous = 300s
Total autonomous = 900s, manual = 900s