Find Top Performing Driver

Problem

Table: Drivers

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| driver_id    | int     |
| name         | varchar |
| age          | int     |
| experience   | int     |
| accidents    | int     |
+--------------+---------+
(driver_id) is the unique key for this table.
Each row 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 unique key for this table.
Each row 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 unique key for this table.
Each row 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 by fuel_type inascending order.

The result format is in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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.

Solution

Method 1 – SQL: Ranking and Filtering

Intuition

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.

Approach

Use window functions to find the maximum experience and minimum accidents among all drivers.
Select drivers whose experience equals the maximum and accidents equals the minimum.
Return their driver_id and name.

Code

1
2
3
4
SELECT driver_id, name
FROM Drivers
WHERE experience = (SELECT MAX(experience) FROM Drivers)
  AND accidents = (SELECT MIN(accidents) FROM Drivers WHERE experience = (SELECT MAX(experience) FROM Drivers));

1
2
3
4
SELECT driver_id, name
FROM Drivers
WHERE experience = (SELECT MAX(experience) FROM Drivers)
  AND accidents = (SELECT MIN(accidents) FROM Drivers WHERE experience = (SELECT MAX(experience) FROM Drivers));

1
2
3
4
5
6
7
import pandas as pd

def find_top_performing_driver(drivers: pd.DataFrame) -> pd.DataFrame:
    max_exp = drivers['experience'].max()
    min_acc = drivers[drivers['experience'] == max_exp]['accidents'].min()
    res = drivers[(drivers['experience'] == max_exp) & (drivers['accidents'] == min_acc)]
    return res[['driver_id', 'name']]

Complexity

⏰ Time complexity: O(n) — n = number of drivers.
🧺 Space complexity: O(n) — For storing intermediate results.

Problem#

Examples#

Example 1:#

Solution#

Method 1 – SQL: Ranking and Filtering#

Intuition#

Approach#

Code#

Complexity#