SQL문제풀기

[SQL 문제 풀이] Find Drivers with Improved Fuel Efficiency (연비가 향상된 드라이버 찾기)

Jul 03, 2025

[SQL 문제 풀이] Find Drivers with Improved Fuel Efficiency (연비가 향상된 드라이버 찾기)

Contents

내가 작성한 쿼리

Find Drivers with Improved Fuel Efficiency - LeetCode

Can you solve this real interview question? Find Drivers with Improved Fuel Efficiency - 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. 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: * Alice Johnson (driver_id = 1): * First half trips (Jan-Jun): Feb 15 (120.5/10.2 = 11.81), Mar 20 (200.0/16.5 = 12.12) * First half average efficiency: (11.81 + 12.12) / 2 = 11.97 * Second half trips (Jul-Dec): Aug 10 (150.0/11.0 = 13.64), Sep 25 (180.0/12.5 = 14.40) * Second half average efficiency: (13.64 + 14.40) / 2 = 14.02 * Efficiency improvement: 14.02 - 11.97 = 2.05 * Bob Smith (driver_id = 2): * First half trips: Jan 10 (100.0/9.0 = 11.11), Apr 15 (250.0/22.0 = 11.36) * First half average efficiency: (11.11 + 11.36) / 2 = 11.24 * Second half trips: Oct 5 (200.0/15.0 = 13.33) * Second half average efficiency: 13.33 * Efficiency improvement: 13.33 - 11.24 = 2.09 * Drivers not included: * Carol Davis (driver_id = 3): Only has trips in first half (Mar, May) * David Wilson (driver_id = 4): Only has trips in second half (Jul, Nov) * Emma Brown (driver_id = 5): Only has trips in first half (Feb) The output table is ordered by efficiency improvement in descending order then by name in ascending order.

Find Drivers with Improved Fuel Efficiency - LeetCode

https://leetcode.com/problems/find-drivers-with-improved-fuel-efficiency/description/

Find Drivers with Improved Fuel Efficiency - LeetCode

notion image


상반기와 하반기의 평균 연비를 비교하여 연비가 개선된 운전자를 찾기 위한 해결책을 작성하세요.

* distance_km / fuel_consumed로 연료 효율 계산
* 상반기: 1월부터 6월까지, 하반기: 7월부터 12월까지
* 상,하반기에 모두 여행한 운전자만 포함하세요
* 효율성 향상을 (second_half_avg - first_half_avg)로 계산합니다
* 모든 결과를 소수점 이하 두 자리로 반올림하세요

결과 테이블을 내림차순으로 효율성 향상에 따라 정렬한 다음,
드라이버 이름을 오름차순으로 정렬하여 반환합니다.

내가 작성한 쿼리

MySQL, Oracle


WITH
-- 상반기 운전자 id, 연비 CTE
FirstHalf AS (
    SELECT
        driver_id,
        AVG(distance_km / fuel_consumed) AS first_half_avg
    FROM trips
    WHERE EXTRACT(MONTH FROM trip_date) BETWEEN 1 AND 6
    GROUP BY driver_id
),
-- 하반기 운전자 id, 연비 CTE
SecondHalf AS (
    SELECT
        driver_id,
        AVG(distance_km / fuel_consumed) AS second_half_avg
    FROM trips
    WHERE EXTRACT(MONTH FROM trip_date) BETWEEN 7 AND 12
    GROUP BY driver_id
),
-- 상, 하반기 운전자 id, 연비, 연비 효율 CTE
Efficiency AS (
    SELECT
        f.driver_id,
        f.first_half_avg,
        s.second_half_avg,
        s.second_half_avg - f.first_half_avg AS efficiency_improvement
    FROM FirstHalf f
    JOIN SecondHalf s ON f.driver_id = s.driver_id
)

SELECT
    d.driver_id,
    d.driver_name,
    ROUND(e.first_half_avg, 2) AS first_half_avg,
    ROUND(e.second_half_avg, 2) AS second_half_avg,
    ROUND(e.efficiency_improvement, 2) AS efficiency_improvement
FROM Efficiency e
JOIN drivers d ON e.driver_id = d.driver_id
WHERE e.efficiency_improvement > 0
ORDER BY efficiency_improvement DESC, driver_name ASC;

Share article

More articles

[JAVA 문제 풀이] 362. 삼각 달팽이

[JAVA 문제 풀이] 362. 삼각 달팽이

[JAVA 문제 풀이] 361. 연속된 부분 수열의 합

[JAVA 문제 풀이] 361. 연속된 부분 수열의 합

[SQL 문제 풀이] Movie Rating (영화 등급)

[SQL 문제 풀이] Movie Rating (영화 등급)

[JAVA 문제 풀이] 360. Find the K-th Character in String Game I [스트링 게임 I에서 K번째 문자 찾기]

[JAVA 문제 풀이] 360. Find the K-th Character in String Game I [스트링 게임 I에서 K번째 문자 찾기]

stupefyee

RSS·Powered by Inblog