Fuel-Type Top Driver Performance

Medium

A transportation analytics platform needs a per-fuel leaderboard of driver performance. Tables: Drivers(driver_id, name, age, experience, accidents) Vehicles(vehicle_id, driver_id, model, fuel_type, mileage) Trips(trip_id, vehicle_id, distance, duration, rating) Join semantics: A trip belongs to a vehicle through vehicle_id. A vehicle belongs to a driver through driver_id. Only joined rows across all three tables contribute to metrics. Metric definitions per (fuel_type, driver_id): rating: average of trip ratings rounded to 2 decimals (HALF_UP) distance: sum of trip distances Ranking rules inside each fuel_type: Higher rounded rating ranks first. If rating ties, higher total distance ranks first. If still tied, fewer accidents ranks first. Task: Return exactly one top driver row for each fuel_type that has at least one joined trip. Output columns (exact order): fuel_type driver_id rating distance Final ordering: fuel_type ascending Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Drivers:
| driver_id | name    | age | experience | accidents |
|-----------|---------|-----|------------|-----------|
| 1         | Alice   | 34  | 10         | 1         |
| 2         | Bob     | 45  | 20         | 3         |
| 3         | Charlie | 28  | 5          | 0         |

Vehicles:
| vehicle_id | driver_id | model | fuel_type | mileage |
|------------|-----------|-------|-----------|---------|
| 100        | 1         | Sedan | Gasoline  | 20000   |
| 101        | 2         | SUV   | Electric  | 30000   |
| 102        | 3         | Coupe | Gasoline  | 15000   |

Trips:
| 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":"Electric","driver_id":2,"rating":4.5,"distance":180},
  {"fuel_type":"Gasoline","driver_id":3,"rating":5.0,"distance":100}
]

Explanation

Gasoline winner is driver 3 due to higher rating. Electric has only one participating driver.

Example 2

Input

Drivers:
| driver_id | name  | age | experience | accidents |
|-----------|-------|-----|------------|-----------|
| 10        | Nora  | 38  | 12         | 2         |
| 11        | Omar  | 40  | 13         | 0         |

Vehicles:
| vehicle_id | driver_id | model | fuel_type | mileage |
|------------|-----------|-------|-----------|---------|
| 200        | 10        | H1    | Hybrid    | 18000   |
| 201        | 11        | H2    | Hybrid    | 19000   |

Trips:
| trip_id | vehicle_id | distance | duration | rating |
|---------|------------|----------|----------|--------|
| 300     | 200        | 70       | 40       | 4      |
| 301     | 200        | 50       | 35       | 5      |
| 302     | 201        | 60       | 35       | 5      |
| 303     | 201        | 40       | 30       | 4      |

Output

[
  {"fuel_type":"Hybrid","driver_id":10,"rating":4.5,"distance":120}
]

Explanation

Both drivers have rating 4.50, so the larger total distance (120 vs 100) decides the winner.

Example 3

Input

Drivers:
| driver_id | name | age | experience | accidents |
|-----------|------|-----|------------|-----------|
| 20        | Rui  | 36  | 11         | 4         |
| 21        | Sia  | 35  | 10         | 1         |

Vehicles:
| vehicle_id | driver_id | model | fuel_type | mileage |
|------------|-----------|-------|-----------|---------|
| 300        | 20        | D1    | Diesel    | 26000   |
| 301        | 21        | D2    | Diesel    | 25000   |

Trips:
| trip_id | vehicle_id | distance | duration | rating |
|---------|------------|----------|----------|--------|
| 400     | 300        | 60       | 30       | 5      |
| 401     | 300        | 40       | 25       | 4      |
| 402     | 301        | 70       | 35       | 4      |
| 403     | 301        | 30       | 20       | 5      |

Output

[
  {"fuel_type":"Diesel","driver_id":21,"rating":4.5,"distance":100}
]

Explanation

Rating and distance tie, so fewer accidents determines the winner.

Constraints

1 <= rows in Drivers <= 200000
1 <= rows in Vehicles <= 400000
1 <= rows in Trips <= 800000
driver_id is unique in Drivers
vehicle_id is unique in Vehicles
trip_id is unique in Trips
1 <= rating <= 5 for all trip rows
0 <= distance and 0 <= duration
accidents is a non-negative integer
Use rounded rating (2 decimals, HALF_UP) for ranking and output
SQL submissions run on SQLite 3.27.2
Pandas submissions must return columns [fuel_type, driver_id, rating, distance]

Drivers: | driver_id | name | age | experience | accidents | |-----------|---------|-----|------------|-----------| | 1 | Alice | 34 | 10 | 1 | | 2 | Bob | 45 | 20 | 3 | | 3 | Charlie | 28 | 5 | 0 | Vehicles: | vehicle_id | driver_id | model | fuel_type | mileage | |------------|-----------|-------|-----------|---------| | 100 | 1 | Sedan | Gasoline | 20000 | | 101 | 2 | SUV | Electric | 30000 | | 102 | 3 | Coupe | Gasoline | 15000 | Trips: | 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 |

Drivers: | driver_id | name | age | experience | accidents | |-----------|-------|-----|------------|-----------| | 10 | Nora | 38 | 12 | 2 | | 11 | Omar | 40 | 13 | 0 | Vehicles: | vehicle_id | driver_id | model | fuel_type | mileage | |------------|-----------|-------|-----------|---------| | 200 | 10 | H1 | Hybrid | 18000 | | 201 | 11 | H2 | Hybrid | 19000 | Trips: | trip_id | vehicle_id | distance | duration | rating | |---------|------------|----------|----------|--------| | 300 | 200 | 70 | 40 | 4 | | 301 | 200 | 50 | 35 | 5 | | 302 | 201 | 60 | 35 | 5 | | 303 | 201 | 40 | 30 | 4 |

Drivers: | driver_id | name | age | experience | accidents | |-----------|------|-----|------------|-----------| | 20 | Rui | 36 | 11 | 4 | | 21 | Sia | 35 | 10 | 1 | Vehicles: | vehicle_id | driver_id | model | fuel_type | mileage | |------------|-----------|-------|-----------|---------| | 300 | 20 | D1 | Diesel | 26000 | | 301 | 21 | D2 | Diesel | 25000 | Trips: | trip_id | vehicle_id | distance | duration | rating | |---------|------------|----------|----------|--------| | 400 | 300 | 60 | 30 | 5 | | 401 | 300 | 40 | 25 | 4 | | 402 | 301 | 70 | 35 | 4 | | 403 | 301 | 30 | 20 | 5 |