Autonomous vs Manual Drive Time

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

1. Consider each row in vehicle_state_events as the start of a state interval.
2. The interval ends at the next event timestamp for the same vehicle.
3. If there is no next event for the vehicle, end the interval at trip_end_ts from vehicle_trips.
4. Split time by vehicle_id + trip_date (derived from trip_start_ts), and return two columns: autonomous_seconds and manual_seconds.
5. Ignore any intervals where the computed end time is <= start time (bad data / duplicates).
6. Output should include one row per vehicle_id, trip_date.