awesome-leetcode-sql/leetcode/medium/3601. Find Drivers with Improved Fuel Efficiency.sql at 9463e002b7dc40cc9637dcb8581c33d20f34849f · Ivanbyone/awesome-leetcode-sql · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
/*
Question 3601. Find Drivers with Improved Fuel Efficiency
Link: https://leetcode.com/problems/find-drivers-with-improved-fuel-efficiency/description/?envType=problem-list-v2&envId=database

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.

Calculate fuel efficiency as distance_km / fuel_consumed for each trip
First half: January to June, Second half: July to December
Only include drivers who have trips in both halves of the year
Calculate the efficiency improvement as (second_half_avg - first_half_avg)
Round all results to 2 decimal places
Return the result table ordered by efficiency improvement in descending order, then by driver name in ascending order.
*/

WITH first_avg AS (
    SELECT
        driver_id,
        AVG(distance_km::numeric / fuel_consumed) AS first_half_avg
    FROM trips
    WHERE trip_date < '2023-07-01'
    GROUP BY driver_id
),

second_avg AS (
    SELECT
        driver_id,
        AVG(distance_km::numeric / fuel_consumed) AS second_half_avg
    FROM trips
    WHERE trip_date >= '2023-07-01'
    GROUP BY driver_id
)

SELECT
    d.driver_id,
    d.driver_name,
    ROUND(fa.first_half_avg, 2) AS first_half_avg,
    ROUND(sa.second_half_avg, 2) AS second_half_avg,
    ROUND((sa.second_half_avg - fa.first_half_avg), 2) AS efficiency_improvement
FROM first_avg AS fa
INNER JOIN
    second_avg AS sa
    ON fa.driver_id = sa.driver_id
LEFT JOIN
    drivers AS d
    ON fa.driver_id = d.driver_id
WHERE (sa.second_half_avg - fa.first_half_avg) > 0
ORDER BY efficiency_improvement DESC, d.driver_name ASC