LeetCode-in-Java

3601. Find Drivers with Improved Fuel Efficiency

Medium

Table: drivers

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| driver_id   | int     |
| driver_name | varchar |
+-------------+---------+
driver_id is the unique identifier for this table. Each row contains information about a driver. 

Table: trips

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| trip_id       | int     |
| driver_id     | int     |
| trip_date     | date    |
| distance_km   | decimal |
| fuel_consumed | decimal |
+---------------+---------+
trip_id is the unique identifier for this table.
Each row represents a trip made by a driver, including the distance traveled and fuel consumed for that trip. 

Write a solution to find drivers whose fuel efficiency has improved by comparing their average fuel efficiency in the first half of the year with the second half of the year.

Return the result table ordered by efficiency improvement in descending order, then by driver name in ascending order.

The result format is in the following example.

Example:

Input:

drivers table:

+-----------+---------------+
| driver_id | driver_name   |
+-----------+---------------+
| 1         | Alice Johnson |
| 2         | Bob Smith     |
| 3         | Carol Davis   |
| 4         | David Wilson  |
| 5         | Emma Brown    |
+-----------+---------------+ 

trips table:

+---------+-----------+------------+-------------+---------------+
| trip_id | driver_id | trip_date  | distance_km | fuel_consumed |
+---------+-----------+------------+-------------+---------------+
| 1       | 1         | 2023-02-15 | 120.5       | 10.2          |
| 2       | 1         | 2023-03-20 | 200.0       | 16.5          |
| 3       | 1         | 2023-08-10 | 150.0       | 11.0          |
| 4       | 1         | 2023-09-25 | 180.0       | 12.5          |
| 5       | 2         | 2023-01-10 | 100.0       | 9.0           |
| 6       | 2         | 2023-04-15 | 250.0       | 22.0          |
| 7       | 2         | 2023-10-05 | 200.0       | 15.0          |
| 8       | 3         | 2023-03-12 | 80.0        | 8.5           |
| 9       | 3         | 2023-05-18 | 90.0        | 9.2           |
| 10      | 4         | 2023-07-22 | 160.0       | 12.8          |
| 11      | 4         | 2023-11-30 | 140.0       | 11.0          |
| 12      | 5         | 2023-02-28 | 110.0       | 11.5          |
+---------+-----------+------------+-------------+---------------+ 

Output:

+-----------+---------------+------------------+-------------------+------------------------+
| driver_id | driver_name   | first_half_avg   | second_half_avg   | efficiency_improvement |
+-----------+---------------+------------------+-------------------+------------------------+
| 2         | Bob Smith     | 11.24            | 13.33             | 2.10                   |
| 1         | Alice Johnson | 11.97            | 14.02             | 2.05                   |
+-----------+---------------+------------------+-------------------+------------------------+ 

Explanation:

The output table is ordered by efficiency improvement in descending order then by name in ascending order.

Solution

# Write your MySQL query statement below
WITH main_process AS (
    SELECT 
        t.driver_id,
        d.driver_name,
        ROUND(AVG(t.distance_km / t.fuel_consumed), 2) AS first_half_avg,
        ROUND(AVG(t1.distance_km / t1.fuel_consumed), 2) AS second_half_avg,
        ROUND(
            AVG(t1.distance_km / t1.fuel_consumed) - AVG(t.distance_km / t.fuel_consumed),
            2
        ) AS efficiency_improvement
    FROM 
        trips t
        INNER JOIN trips t1 ON t.driver_id = t1.driver_id
        INNER JOIN drivers d ON t.driver_id = d.driver_id
        AND EXTRACT(MONTH FROM t.trip_date) BETWEEN 1 AND 6
        AND EXTRACT(MONTH FROM t1.trip_date) BETWEEN 7 AND 12
    GROUP BY 
        t.driver_id,
        d.driver_name
    ORDER BY 
        efficiency_improvement DESC,
        d.driver_name ASC
)
SELECT 
    driver_id,
    driver_name,
    first_half_avg,
    second_half_avg,
    efficiency_improvement
FROM main_process
WHERE efficiency_improvement > 0;