+--------------+---------+
|Column Name |Type|+--------------+---------+
| driver_id | int || name | varchar || age | int || experience | int || accidents | int |+--------------+---------+
(driver_id) is the uniquekeyfor this table.
Eachrow includes a driver's ID, their name, age, years of driving experience, and the number of accidents they've had.
Table: Vehicles
1
2
3
4
5
6
7
8
9
+--------------+---------+
| vehicle_id | int || driver_id | int || model | varchar || fuel_type | varchar || mileage | int |+--------------+---------+
(vehicle_id, driver_id, fuel_type) is the uniquekeyfor this table.
Eachrow includes the vehicle's ID, the driver who operates it, the model, fuel type, and mileage.
Table: Trips
1
2
3
4
5
6
7
8
9
+--------------+---------+
| trip_id | int || vehicle_id | int || distance | int || duration | int || rating | int |+--------------+---------+
(trip_id) is the uniquekeyfor this table.
Eachrow includes a trip's ID, the vehicle used, the distance covered (in miles), the trip duration (in minutes), and the passenger's rating (1-5).
Uber is analyzing drivers based on their trips. Write a solution to find the
top-performing driver for each fuel type based on the following criteria:
A driver’s performance is calculated as the average rating across all their trips. Average rating should be rounded to 2 decimal places.
If two drivers have the same average rating, the driver with the longer total distance traveled should be ranked higher.
If there is still a tie , choose the driver with the fewest accidents.
Return the result table ordered byfuel_typeinascendingorder.
Input:
`Drivers` table:+-----------+----------+-----+------------+-----------+| driver_id | name | age | experience | accidents |+-----------+----------+-----+------------+-----------+|1| Alice |34|10|1||2| Bob |45|20|3||3| Charlie |28|5|0|+-----------+----------+-----+------------+-----------+`Vehicles` table:+------------+-----------+---------+-----------+---------+| vehicle_id | driver_id | model | fuel_type | mileage |+------------+-----------+---------+-----------+---------+|100|1| Sedan | Gasoline |20000||101|2| SUV | Electric |30000||102|3| Coupe | Gasoline |15000|+------------+-----------+---------+-----------+---------+`Trips` table:+---------+------------+----------+----------+--------+| trip_id | vehicle_id | distance | duration | rating |+---------+------------+----------+----------+--------+|201|100|50|30|5||202|100|30|20|4||203|101|100|60|4||204|101|80|50|5||205|102|40|30|5||206|102|60|40|5|+---------+------------+----------+----------+--------+Output:
+-----------+-----------+--------+----------+| fuel_type | driver_id | rating | distance |+-----------+-----------+--------+----------+| Electric |2|4.50|180|| Gasoline |3|5.00|100|+-----------+-----------+--------+----------+Explanation:
* For fuel type `Gasoline`, both Alice(Driver 1) and Charlie(Driver 3) have trips. Charlie has an average rating of 5.0,while Alice has 4.5. Therefore, Charlie is selected.* For fuel type `Electric`, Bob (Driver 2)is the only driver with an average rating of 4.5, so he is selected.The output table is ordered by `fuel_type`in ascending order.
A top performing driver is one with the highest experience and the fewest accidents. We can use window functions to rank drivers by experience (descending) and accidents (ascending), then select those with both the highest experience and the lowest accidents.
SELECT driver_id, name
FROM Drivers
WHERE experience = (SELECTMAX(experience) FROM Drivers)
AND accidents = (SELECTMIN(accidents) FROM Drivers WHERE experience = (SELECTMAX(experience) FROM Drivers));
1
2
3
4
SELECT driver_id, name
FROM Drivers
WHERE experience = (SELECTMAX(experience) FROM Drivers)
AND accidents = (SELECTMIN(accidents) FROM Drivers WHERE experience = (SELECTMAX(experience) FROM Drivers));