Your question is Vehicle Mode Duration by Trip. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
You are given Waymo Driver state-change logs and trip boundaries. Write a PostgreSQL query to calculate, for each trip, the total time the vehicle spent in autonomous mode versus manual mode. Treat each state row as effective until the next state change within the same trip; for the final state in a trip, use the trip end time. Ignore rows where the state is NULL.
| Column | Type | Description |
|---|---|---|
| trip_idPK | INT | Unique trip identifier |
| vehicle_id | INT | Vehicle identifier |
| route_name | VARCHAR(100) | Waymo service route or area |
| trip_start_ts | TIMESTAMP | Trip start timestamp |
| trip_end_ts | TIMESTAMP | Trip end timestamp |
| Column | Type | Description |
|---|---|---|
| log_idPK | INT | Unique state log identifier |
| trip_id | INT | Trip tied to the state log |
| event_ts | TIMESTAMP | Timestamp when the vehicle state changed |
| vehicle_state | VARCHAR(20) | Vehicle mode such as autonomous or manual |