Tables
erDiagram
drivers {
driver_id int PK
driver_name varchar
}
trips {
trip_id int PK
driver_id int FK
trip_date date
distance_km deciaml
fuel_consumed decimal
}
drivers || -- o{ trips : "trip info"
Mermaid
복사
Question
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.
The result format is in the following example.
DataFlow
•
first half / second half -> group by date
•
sum(km), sum(feul) -> groupby driver_id
Answer
SELECT
t1.driver_id,
d.driver_name,
ROUND(t1.first_trip, 2) AS first_half_avg,
ROUND(t2.second_trip, 2) AS second_half_avg,
ROUND(t2.second_trip - t1.first_trip, 2) AS efficiency_improvement
FROM (
SELECT
driver_id,
AVG(distance_km/fuel_consumed) AS first_trip
FROM trips
WHERE trip_date < "2023-07-01"
GROUP BY driver_id
) t1
INNER JOIN (
SELECT
driver_id,
AVG(distance_km/fuel_consumed) AS second_trip
FROM trips
WHERE trip_date >= "2023-07-01"
GROUP BY driver_id
) t2
ON t1.driver_id = t2.driver_id
AND t2.second_trip > t1.first_trip
INNER JOIN drivers d
ON t1.driver_id = d.driver_id
ORDER BY efficiency_improvement DESC, d.driver_name ASC;
SQL
복사
INNER JOIN 으로 전반기 / 후반기 모두 있는 driver만 추출
효율 상승 분이니까 전반기가 큰 driver만 추출