SQL문제풀기

[SQL 문제 풀이] Find Consistently Improving Employees (지속적으로 개선되는 직원 찾기)

Jun 23, 2025

[SQL 문제 풀이] Find Consistently Improving Employees (지속적으로 개선되는 직원 찾기)

Contents

내가 작성한 쿼리

Find Consistently Improving Employees - LeetCode

Can you solve this real interview question? Find Consistently Improving Employees - Table: employees +-------------+---------+ | Column Name | Type | +-------------+---------+ | employee_id | int | | name | varchar | +-------------+---------+ employee_id is the unique identifier for this table. Each row contains information about an employee. Table: performance_reviews +-------------+------+ | Column Name | Type | +-------------+------+ | review_id | int | | employee_id | int | | review_date | date | | rating | int | +-------------+------+ review_id is the unique identifier for this table. Each row represents a performance review for an employee. The rating is on a scale of 1-5 where 5 is excellent and 1 is poor. Write a solution to find employees who have consistently improved their performance over their last three reviews. * An employee must have at least 3 review to be considered * The employee's last 3 reviews must show strictly increasing ratings (each review better than the previous) * Use the most recent 3 reviews based on review_date for each employee * Calculate the improvement score as the difference between the latest rating and the earliest rating among the last 3 reviews Return the result table ordered by improvement score in descending order, then by name in ascending order. The result format is in the following example. Example: Input: employees table: +-------------+----------------+ | employee_id | name | +-------------+----------------+ | 1 | Alice Johnson | | 2 | Bob Smith | | 3 | Carol Davis | | 4 | David Wilson | | 5 | Emma Brown | +-------------+----------------+ performance_reviews table: +-----------+-------------+-------------+--------+ | review_id | employee_id | review_date | rating | +-----------+-------------+-------------+--------+ | 1 | 1 | 2023-01-15 | 2 | | 2 | 1 | 2023-04-15 | 3 | | 3 | 1 | 2023-07-15 | 4 | | 4 | 1 | 2023-10-15 | 5 | | 5 | 2 | 2023-02-01 | 3 | | 6 | 2 | 2023-05-01 | 2 | | 7 | 2 | 2023-08-01 | 4 | | 8 | 2 | 2023-11-01 | 5 | | 9 | 3 | 2023-03-10 | 1 | | 10 | 3 | 2023-06-10 | 2 | | 11 | 3 | 2023-09-10 | 3 | | 12 | 3 | 2023-12-10 | 4 | | 13 | 4 | 2023-01-20 | 4 | | 14 | 4 | 2023-04-20 | 4 | | 15 | 4 | 2023-07-20 | 4 | | 16 | 5 | 2023-02-15 | 3 | | 17 | 5 | 2023-05-15 | 2 | +-----------+-------------+-------------+--------+ Output: +-------------+----------------+-------------------+ | employee_id | name | improvement_score | +-------------+----------------+-------------------+ | 2 | Bob Smith | 3 | | 1 | Alice Johnson | 2 | | 3 | Carol Davis | 2 | +-------------+----------------+-------------------+ Explanation: * Alice Johnson (employee_id = 1): * Has 4 reviews with ratings: 2, 3, 4, 5 * Last 3 reviews (by date): 2023-04-15 (3), 2023-07-15 (4), 2023-10-15 (5) * Ratings are strictly increasing: 3 → 4 → 5 * Improvement score: 5 - 3 = 2 * Carol Davis (employee_id = 3): * Has 4 reviews with ratings: 1, 2, 3, 4 * Last 3 reviews (by date): 2023-06-10 (2), 2023-09-10 (3), 2023-12-10 (4) * Ratings are strictly increasing: 2 → 3 → 4 * Improvement score: 4 - 2 = 2 * Bob Smith (employee_id = 2): * Has 4 reviews with ratings: 3, 2, 4, 5 * Last 3 reviews (by date): 2023-05-01 (2), 2023-08-01 (4), 2023-11-01 (5) * Ratings are strictly increasing: 2 → 4 → 5 * Improvement score: 5 - 2 = 3 * Employees not included: * David Wilson (employee_id = 4): Last 3 reviews are all 4 (no improvement) * Emma Brown (employee_id = 5): Only has 2 reviews (needs at least 3) The output table is ordered by improvement_score in descending order, then by name in ascending order.

Find Consistently Improving Employees - LeetCode

https://leetcode.com/problems/find-consistently-improving-employees/description/

Find Consistently Improving Employees - LeetCode

notion image


지난 세 번의 리뷰를 통해 꾸준히 성과를 향상시킨 직원들을 찾기 위한 솔루션을 작성하세요.

* 직원이 고려해야 할 리뷰가 최소 3개 이상 있어야 합니다
* 직원의 마지막 3개 리뷰는 엄격하게 증가하는 평점을 표시해야 합니다(각 리뷰는 이전보다 더 나은 평점)
* 각 직원에 대해 리뷰 날짜를 기준으로 최신 3개의 리뷰를 사용하세요
* 지난 3개의 리뷰 중 최신 평점과 가장 이른 평점의 차이로 개선 점수를 계산합니다

결과 표를 내림차순으로 개선 점수 순으로, 그리고 오름차순으로 이름 순으로 반환합니다.

내가 작성한 쿼리

MySQL, Oracle


-- 직원별 리뷰에 순위를 부여 (가장 최근 리뷰가 rn = 1)
WITH RankedReviews AS (
    SELECT 
        employee_id,
        review_date,
        rating,
        ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY review_date DESC) AS rn
    FROM performance_reviews
),

-- 최근 3개의 리뷰만 필터링
LastThreeReviews AS (
    SELECT * 
    FROM RankedReviews
    WHERE rn <= 3
),

-- 3개의 리뷰를 하나의 행으로 변환 (가장 오래된 리뷰 → 중간 → 가장 최신)
Grouped AS (
    SELECT 
        employee_id,
        MIN(CASE WHEN rn = 3 THEN rating END) AS oldest_rating,
        MIN(CASE WHEN rn = 2 THEN rating END) AS middle_rating,
        MIN(CASE WHEN rn = 1 THEN rating END) AS latest_rating
    FROM LastThreeReviews
    GROUP BY employee_id
),

-- 리뷰 점수가 점점 향상된 직원만 필터링 + 개선 점수 계산
Improved AS (
    SELECT 
        g.employee_id,
        e.name,
        (latest_rating - oldest_rating) AS improvement_score
    FROM Grouped g
    JOIN employees e ON e.employee_id = g.employee_id
    WHERE 
        oldest_rating < middle_rating AND
        middle_rating < latest_rating
)

-- 최종 결과 출력: 개선 점수 내림차순, 이름 오름차순
SELECT * 
FROM Improved
ORDER BY improvement_score DESC, name ASC;

Share article

stupefyee

RSS·Powered by Inblog