Task
Waymo operations wants a daily leaderboard of the longest completed rides for each vehicle. Write a SQL query to return the top 3 rides per vehicle for each calendar day.
Requirements
- Use
start_time to derive the ride date.
- Only include rides where
status = 'completed' and distance_miles is not null.
- Rank rides within each
(vehicle_id, ride_date) group by distance_miles descending.
- Break ties by earlier
start_time, then smaller ride_id.
- Return only ranks 1 through 3.
- Include vehicle and depot details in the output.
Table Definitions
vehicles
| column | type | description |
|---|
| vehicle_id | INT | Unique vehicle identifier |
| vehicle_name | VARCHAR(100) | Display name for the vehicle |
| depot_id | INT | Assigned depot for the vehicle |
depots
| column | type | description |
|---|
| depot_id | INT | Unique depot identifier |
| depot_name | VARCHAR(100) | Depot name |
rides
| column | type | description |
|---|
| ride_id | INT | Unique ride identifier |
| vehicle_id | INT | Vehicle that served the ride |
| start_time | TIMESTAMP | Ride start timestamp |
| end_time | TIMESTAMP | Ride end timestamp |
| distance_miles | DECIMAL(6,2) | Ride distance in miles |
| status | VARCHAR(40) | Ride status |
