Find Top Performing Driver - Problem

Given three tables Drivers, Vehicles, and Trips, find the top-performing driver for each fuel type based on specific performance criteria.

Performance Calculation Rules:

  • Primary: Average rating across all trips (rounded to 2 decimal places)
  • Tiebreaker 1: Driver with longer total distance traveled
  • Tiebreaker 2: Driver with fewest accidents

Return results ordered by fuel_type in ascending order, showing fuel_type, driver_id, rating (average), and distance (total).

Table Schema

Drivers
Column Name Type Description
driver_id PK int Unique driver identifier
name varchar Driver's name
age int Driver's age
experience int Years of driving experience
accidents int Number of accidents
Primary Key: driver_id
Vehicles
Column Name Type Description
vehicle_id PK int Unique vehicle identifier
driver_id int Driver operating the vehicle
model varchar Vehicle model
fuel_type varchar Type of fuel (Gasoline, Electric, etc.)
mileage int Vehicle mileage
Primary Key: vehicle_id
Trips
Column Name Type Description
trip_id PK int Unique trip identifier
vehicle_id int Vehicle used for the trip
distance int Distance covered in miles
duration int Trip duration in minutes
rating int Passenger rating (1-5)
Primary Key: trip_id

Input & Output

Example 1 — Multiple Drivers per Fuel Type
Input Tables:
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 driver_id rating distance
Electric 2 4.5 180
Gasoline 3 5 100
💡 Note:

For Electric: Bob (driver 2) is the only driver with average rating 4.50 and total distance 180.

For Gasoline: Alice (driver 1) has rating 4.50 with distance 80, while Charlie (driver 3) has rating 5.00 with distance 100. Charlie wins with higher rating.
Example 2 — Tiebreaker Scenarios
Input Tables:
Drivers
driver_id name age experience accidents
1 Alice 30 8 2
2 Bob 35 12 1
Vehicles
vehicle_id driver_id model fuel_type mileage
100 1 Sedan Gasoline 25000
101 2 Hatchback Gasoline 18000
Trips
trip_id vehicle_id distance duration rating
201 100 60 40 4
202 101 40 30 4
Output:
fuel_type driver_id rating distance
Gasoline 1 4 60
💡 Note:

Both drivers have the same average rating (4.00). Alice has higher total distance (60 vs 40), so she wins the tiebreaker despite having more accidents.

Constraints

  • 1 ≤ driver_id ≤ 1000
  • 1 ≤ vehicle_id ≤ 1000
  • 1 ≤ trip_id ≤ 10000
  • rating is between 1 and 5
  • fuel_type contains only valid fuel types
  • Each driver has at least one trip

Visualization

Tap to expand
Find Top Performing Driver by Fuel TypeInput TablesDriversAlice (1)Bob (2)Charlie (3)VehiclesGas-1Elec-2Gas-3TripsRating: 4-5DistanceDurationJOIN +RANK()Top Performersfuel_typedriver_idratingdistanceElectric24.50180Gasoline35.00100Ranking Criteria:1. Highest Average Rating2. Longest Total Distance3. Fewest Accidents
Understanding the Visualization
1
Join
Combine Drivers, Vehicles, and Trips tables
2
Aggregate
Calculate performance metrics per driver
3
Rank
Find top performer per fuel type
Key Takeaway
🎯 Key Insight: Use RANK() with PARTITION BY fuel_type to handle complex multi-criteria ranking within groups

Related Problems

Asked in
Uber 15 Lyft 8 Amazon 12
25.0K Views
Medium Frequency
~18 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen