Welcome to Subscribe On Youtube

3308. Find Top Performing Driver 🔒

Description

Table: Drivers

+--++
\| driver_id    \| int     \|
\| name         \| varchar \|
\| age          \| int     \|
\| experience   \| int     \|
\| accidents    \| int     \|
+--++
\| vehicle_id   \| int     \|
\| driver_id    \| int     \|
\| model        \| varchar \|
\| fuel_type    \| varchar \|
\| mileage      \| int     \|
+--++
\| trip_id      \| int     \|
\| vehicle_id   \| int     \|
\| distance     \| int     \|
\| duration     \| int     \|
\| rating       \| int     \|
+--+--+--++--+--++--+--++--+--+--+--+--+--+++-+--+
\| trip_id \| vehicle_id \| distance \| duration \| rating \|
+-+++-+--+

Output:

+--+--+--+-+
\| 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.

</div>

Solutions

Solution 1: Equi-join + Grouping + Window Function

We can use equi-join to join the Drivers table with the Vehicles table on driver_id, and then join with the Trips table on vehicle_id. Next, we group by fuel_type and driver_id to calculate each driver’s average rating, total mileage, and total accident count. Then, using the RANK() window function, we rank the drivers of each fuel type in descending order of rating, descending order of total mileage, and ascending order of total accident count. Finally, we filter out the driver ranked 1 for each fuel type.

• SQL

• # Write your MySQL query statement below
  WITH
      T AS (
          SELECT
              fuel_type,
              driver_id,
              ROUND(AVG(rating), 2) rating,
              SUM(distance) distance,
              SUM(accidents) accidents
          FROM
              Drivers
              JOIN Vehicles USING (driver_id)
              JOIN Trips USING (vehicle_id)
          GROUP BY fuel_type, driver_id
      ),
      P AS (
          SELECT
              *,
              RANK() OVER (
                  PARTITION BY fuel_type
                  ORDER BY rating DESC, distance DESC, accidents
              ) rk
          FROM T
      )
  SELECT fuel_type, driver_id, rating, distance
  FROM P
  WHERE rk = 1
  ORDER BY 1;
  
  

All Problems

All Solutions