Your question is Average Vehicle Idle Time by Zone. 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 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 |