Welcome to the SQL screen.
The question is on your right: Vehicle Mode Duration by Trip. Read through the requirements and the two tables first.
Run and submit your code as often as you need. You also have five interviewer messages this session - want to talk through your approach, or are you ready to start coding?
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 |