Welcome to the SQL screen.
The question is on your right: Average Vehicle Idle Time by Zone. 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 ride activity and Waymo Driver vehicle status logs. Write a SQL query to find the average idle time in minutes per vehicle for vehicles operating in a specific geographic zone, using 'Downtown' as the target zone.
Treat a vehicle as idle when its status is 'idle'. Only count idle status records that fall between a ride's end time and the next ride's start time for the same vehicle. Return one row per vehicle in the target zone, along with its average idle time, ordered from highest to lowest average idle time.
| Column | Type | Description |
|---|---|---|
| ride_idPK | INT | Unique ride identifier |
| vehicle_id | INT | Vehicle identifier |
| zone_name | VARCHAR(50) | Geographic zone where the ride occurred |
| ride_start_ts | TIMESTAMP | Ride start timestamp |
| ride_end_ts | TIMESTAMP | Ride end timestamp |
| Column | Type | Description |
|---|---|---|
| status_idPK | INT | Unique vehicle status event identifier |
| vehicle_id | INT | Vehicle identifier |
| status | VARCHAR(20) | Vehicle status at the event timestamp |
| status_ts | TIMESTAMP | Timestamp of the status event |