Find Top Performing Driver - Practice Coding Problems

Find Top Performing Driver - Problem

Database Medium

Find the Top Performing Driver

You're working as a data analyst for Uber and need to identify the best driver for each fuel type based on their trip performance. This involves analyzing driver ratings, total distance traveled, and accident history across multiple database tables.

Given three tables:
• Drivers - Contains driver information including accidents
• Vehicles - Maps drivers to vehicles with fuel types
• Trips - Contains trip data with ratings and distances

Performance Ranking Criteria:
1. Primary: Highest average rating (rounded to 2 decimal places)
2. Tiebreaker 1: Longest total distance traveled
3. Tiebreaker 2: Fewest accidents

Return one top driver per fuel type, ordered by fuel type alphabetically.

Input & Output

example_1.sql — Basic Example

$ Input: Drivers: [(1,'Alice',34,10,1), (2,'Bob',45,20,3), (3,'Charlie',28,5,0)]\nVehicles: [(100,1,'Sedan','Gasoline',20000), (101,2,'SUV','Electric',30000), (102,3,'Coupe','Gasoline',15000)]\nTrips: [(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: [('Electric',2,4.50,180), ('Gasoline',3,5.00,100)]

💡 Note: For Electric: Driver 2 (Bob) has avg rating 4.50 with 180 total distance. For Gasoline: Driver 3 (Charlie) beats Driver 1 (Alice) with 5.00 vs 4.50 avg rating.

example_2.sql — Tie Breaking by Distance

$ Input: Same drivers, but Alice and Charlie both have 4.50 rating, Charlie has more distance

› Output: [('Gasoline',3,4.50,120)]

💡 Note: When ratings are tied, the driver with longer total distance wins the tiebreaker.

example_3.sql — Tie Breaking by Accidents

$ Input: Same drivers with same rating and distance, but different accident counts

› Output: [('Gasoline',3,4.50,100)]

💡 Note: When rating and distance are tied, the driver with fewer accidents wins (Charlie has 0 vs Alice's 1).

Visualization

Tap to expand

Understanding the Visualization

Data Integration

JOIN driver, vehicle, and trip data to create complete performance records

Performance Metrics

Calculate average rating and total distance for each driver per fuel type

Ranking Within Categories

Use RANK() OVER PARTITION BY fuel_type to rank drivers within each category

Champion Selection

Select the #1 ranked driver from each fuel type category

Key Takeaway

🎯 Key Insight: Window functions with PARTITION BY allow efficient ranking within categories, eliminating the need for complex subqueries and manual tie-breaking logic.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single scan with sorting for ranking within each partition

⚡ Linearithmic

Space Complexity

O(n)

Space for intermediate grouped results and ranking

⚡ Linearithmic Space

Constraints

1 ≤ drivers.length ≤ 10³
1 ≤ vehicles.length ≤ 10³
1 ≤ trips.length ≤ 10⁴
1 ≤ rating ≤ 5
0 ≤ accidents ≤ 100
Each driver has at least one trip
Average ratings should be rounded to exactly 2 decimal places

Asked in

U Uber 45 L Lyft 30 a Amazon 25 G Google 20

The optimal solution uses SQL window functions with RANK() OVER PARTITION BY to efficiently rank drivers within each fuel type category. Key steps: JOIN all tables, GROUP BY to calculate metrics, RANK() with proper ORDER BY for tie-breaking, and filter for rank=1.

Common Approaches

Approach	Time	Space	Notes
✓ Window Functions (Optimal)	O(n log n)	O(n)	Use SQL window functions to rank drivers efficiently in one pass
Multiple Subqueries Approach	O(n²)	O(n)	Use separate subqueries for each calculation step

Window Functions (Optimal) — Algorithm Steps

JOIN all three tables to get complete driver trip data
GROUP BY fuel_type and driver to calculate aggregated metrics
Use RANK() OVER with PARTITION BY fuel_type and ORDER BY ranking criteria
Filter to get only rank = 1 drivers
Order final results by fuel_type

Visualization

Tap to expand

Step-by-Step Walkthrough

Join Tables

Single pass to join Drivers, Vehicles, and Trips tables

Group & Aggregate

Calculate avg rating and total distance per driver per fuel type

Partition & Rank

Use RANK() OVER to rank within each fuel_type partition

Filter Top

Select only rank=1 drivers from each partition

Code -

solution.c — C

// Optimal approach using window functions
#include <stdio.h>
#include <string.h>

char* findTopDrivers() {
    static char query[] = ""
        "WITH driver_stats AS ( "
        "    SELECT "
        "        v.fuel_type, "
        "        d.driver_id, "
        "        d.accidents, "
        "        ROUND(AVG(CAST(t.rating AS DECIMAL(10,2))), 2) as avg_rating, "
        "        SUM(t.distance) as total_distance "
        "    FROM Drivers d "
        "    JOIN Vehicles v ON d.driver_id = v.driver_id "
        "    JOIN Trips t ON v.vehicle_id = t.vehicle_id "
        "    GROUP BY v.fuel_type, d.driver_id, d.accidents "
        "), "
        "ranked_drivers AS ( "
        "    SELECT "
        "        fuel_type, "
        "        driver_id, "
        "        avg_rating as rating, "
        "        total_distance as distance, "
        "        RANK() OVER ( "
        "            PARTITION BY fuel_type "
        "            ORDER BY avg_rating DESC, "
        "                     total_distance DESC, "
        "                     accidents ASC "
        "        ) as rn "
        "    FROM driver_stats "
        ") "
        "SELECT fuel_type, driver_id, rating, distance "
        "FROM ranked_drivers "
        "WHERE rn = 1 "
        "ORDER BY fuel_type;";
    return query;
}

int main() {
    printf("%s\n", findTopDrivers());
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single scan with sorting for ranking within each partition

⚡ Linearithmic

Space Complexity

O(n)

Space for intermediate grouped results and ranking

⚡ Linearithmic Space

Constraints

1 ≤ drivers.length ≤ 10³
1 ≤ vehicles.length ≤ 10³
1 ≤ trips.length ≤ 10⁴
1 ≤ rating ≤ 5
0 ≤ accidents ≤ 100
Each driver has at least one trip
Average ratings should be rounded to exactly 2 decimal places

28.4K Views

Medium-High Frequency

~25 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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Window Functions (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler