0/304

00:00:00

Description

Editorial

Fuel-Type Top Driver Performance

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Trips =

[{"trip_id":1,"vehicle_id":1,"distance":50,"duration":53,"rating":5},{"trip_id":2,"vehicle_id":1,"distance":30,"duration":36,"rating":4},{"trip_id":3,"vehicle_id":2,"distance":100,"duration":98,"rating":4},{"trip_id":4,"vehicle_id":2,"distance":80,"duration":81,"rating":5},{"trip_id":5,"vehicle_id":3,"distance":40,"duration":44,"rating":5},{"trip_id":6,"vehicle_id":3,"distance":60,"duration":63,"rating":5}]

Drivers =

[{"driver_id":1,"name":"Driver_1","age":23,"experience":4,"accidents":1},{"driver_id":2,"name":"Driver_2","age":24,"experience":4,"accidents":3},{"driver_id":3,"name":"Driver_3","age":25,"experience":4,"accidents":0}]

Vehicles =

[{"vehicle_id":1,"driver_id":1,"model":"Model_1","fuel_type":"Gasoline","mileage":10001},{"vehicle_id":2,"driver_id":2,"model":"Model_2","fuel_type":"Electric","mileage":10002},{"vehicle_id":3,"driver_id":3,"model":"Model_3","fuel_type":"Gasoline","mileage":10003}]

Fuel-Type Top Driver Performance

Hints

Fuel-Type Top Driver Performance

Hints