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 |