[SQL/오답] 최신 성과 분석: 윈도우 함수 ROW_NUMBER와 Self Join으로 성적 우수자 찾기 (Leetcode3580 Medium)

3580. Find Consistently Improving Employees

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

1. Problem

최근 3번의 성과 리뷰에서 **성적이 지속적으로 상승(Strictly Increasing)**한 직원을 찾아야 한다.

• 조건 1: 리뷰가 최소 3개 이상인 직원만 대상이다.
• 조건 2: 가장 최근 날짜 기준 3개의 리뷰를 분석한다.
• 조건 3: 성적이 최신(1) > 직전(2) > 그 직전(3) 순으로 반드시 엄격하게 상승해야 한다.
• 결과: 최신 성적 - 3번째 전 성적을 개선 점수로 계산하여 내림차순 정렬한다.

---

2. Solution: 최신 데이터 번호 매기기 후 수평 비교

ROW_NUMBER()를 사용하여 날짜 역순으로 번호를 매긴 뒤, 1, 2, 3번 데이터를 조인하여 한 행(Row)으로 통합한다.

-- 1. CTE: 직원별 최신 순서(num)와 전체 리뷰 수(total_count) 계산
WITH employees_table AS (
    SELECT p.employee_id, p.rating, p.num
    FROM (
        SELECT employee_id,
               rating,
               -- 최신 날짜가 1번이 되도록 순번 부여
               ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY review_date DESC) AS num,
               -- 최소 3개 이상 조건을 검증하기 위해 전체 개수 계산
               COUNT(*) OVER (PARTITION BY employee_id) AS total_count
        FROM performance_reviews
    ) p
    -- 최신 3개만 남기고, 리뷰가 3개 미만인 직원은 필터링
    WHERE p.num <= 3 AND p.total_count >= 3
)

-- 2. Self Join을 통해 num 1, 2, 3번을 한 행에 모으기
SELECT 
    t1.employee_id, 
    e1.name, 
    (t1.rating - t3.rating) AS improvement_score
FROM employees_table t1
JOIN employees_table t2 ON t1.employee_id = t2.employee_id AND t2.num = 2
JOIN employees_table t3 ON t1.employee_id = t3.employee_id AND t3.num = 3
JOIN employees e1 ON t1.employee_id = e1.employee_id
-- 3. 엄격한 상승 조건 (최신 1번 > 2번 > 3번) 필터링
WHERE t1.rating > t2.rating AND t2.rating > t3.rating
ORDER BY improvement_score DESC, name ASC;

---

3. Takeaway: 이 쿼리의 객관적 분석

• ROW_NUMBER()와 COUNT(*) OVER의 병행: 단순히 num <= 3만 조건으로 걸 경우, 리뷰가 2개뿐인 사람도 번호가 매겨져 결과에 포함될 수 있다. total_count >= 3 조건을 통해 "최소 3개의 리뷰가 있는 사람"이라는 비즈니스 로직을 서브쿼리 단계에서 선제적으로 해결하였다.
• Self Join을 통한 수평 확장: SQL은 기본적으로 행(Row) 간의 비교에 약하다. 그러나 num이라는 인덱스를 활용해 수직 데이터(1, 2, 3번 행)를 수평 데이터(1번 열, 2번 열, 3번 열)로 변환함으로써 rating1 > rating2 > rating3라는 직관적인 비교를 가능케 하였다.
• 유지보수성: 만약 "최근 5번의 리뷰 중 4번 이상 상승"과 같이 조건이 복잡해진다면 조인 횟수가 과도하게 늘어날 수 있다. 하지만 이 문제처럼 '연속된 3회'라는 명확한 타겟이 있을 때는 이 방식이 가장 빠르고 확실한 해법이 된다.

 

•  

4. 오답 노트

(1) GROUP BY가 끝난 뒤, SELECT에서 윈도우함수로 각 행의 번호를 매길 수 없다. 

### 오답 쿼리 ###
# FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

WITH employees_table AS (
    SELECT p.employee_id, p.rating, p.num
    FROM (SELECT employee_id,
                rating,
                row_number() over (partition by employee_id order by review_date DESC) as num
          FROM performance_reviews
          GROUP BY employee_id
          HAVING COUNT(*) >= 3) p
    WHERE p.num BETWEEN 1 AND 3
)

 

SQL 실행 순서로 본 오류 분석

쿼리 작동 순서: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

서브쿼리 p 내부를 순서대로 뜯어보면,

1. FROM: performance_reviews에서 데이터를 가져온다.
2. GROUP BY: employee_id로 행을 합쳐버립니다. (여기서 행이 사원당 1줄이 됩니다.)
3. HAVING: 리뷰가 3개 이상인 사원만 남긴다.
4. SELECT: row_number()를 계산하려고 합긴다.

문제는 여기서 발생한다. GROUP BY가 실행된 직후에는 행이 이미 사원당 한 줄로 압축되었기 때문에, 그 뒤에 실행되는 row_number()는 각 리뷰(행)에 번호를 매길 수가 없다. 이미 리뷰들이 하나로 뭉쳐졌기 때문이다

### 정답 쿼리 ###
WITH employees_table AS (
    SELECT 
        employee_id, 
        rating, 
        num
    FROM (
        SELECT 
            employee_id,
            rating,
            -- 1. 각 행에 번호를 매깁니다 (SELECT 시점)
            ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY review_date DESC) AS num,
            -- 2. 그룹화하지 않고도 각 사원의 전체 리뷰 수를 계산합니다
            COUNT(*) OVER (PARTITION BY employee_id) AS cnt 
        FROM performance_reviews
    ) p
    -- 3. WHERE 절에서 번호와 개수를 필터링합니다
    WHERE p.num <= 3 AND p.cnt >= 3
)

왜 이렇게 짜야 할까?

1. 데이터 보존: GROUP BY를 쓰면 상세 데이터(리뷰 날짜, 개별 점수)가 사라지지만, WINDOW FUNCTION(OVER)을 쓰면 상세 행을 유지하면서도 집계값(전체 개수)을 옆에 붙일 수 있다.
2. 실행 순서의 정합성: 윈도우 함수는 보통 WHERE나 GROUP BY가 끝난 뒤의 결과 집합에 대해 작동합니다. 따라서 서브쿼리로 감싸서 그 결과를 WHERE에서 필터링하는 지금의 구조가 가장 표준적입니다.

(2) 명시적 조인 조건 : 본인 테이블에서 누구를 가져오는가? 

### 오답 쿼리 ###
SELECT t1.employee_id, e1.name, t1.rating - t3.rating AS improvement_score
FROM employees_table t1
JOIN employees_table t2 ON t1.employee_id = t2.employee_id AND t1.rating > t2.rating
JOIN employees_table t3 ON t2.employee_id = t3.employee_id AND t2.rating > t3.rating
JOIN employees e1 ON t1.employee_id = e1.employee_id
ORDER BY improvement_score DESC, name ASC;

수정 사항 

• num 조건 누락: 현재 JOIN 조건에 t1.rating > t2.rating만 있고 "t1이 1번, t2가 2번, t3가 3번"이라는 조건이 없다. 이렇게 되면 num 순서와 상관없이 점수가 큰 것끼리 마구잡이로 조인될 수 있다.
• 명시적 num 매칭: Self Join을 할 때는 반드시 t2.num = 2처럼 타겟 행의 번호를 명시해야 한다. 그렇지 않으면 num=1인 행이 자기 자신보다 점수가 낮은 num=3인 행과 바로 조인될 수도 있다.

### 정답 쿼리 ###
SELECT 
    t1.employee_id, 
    e1.name, 
    (t1.rating - t3.rating) AS improvement_score
FROM employees_table t1
JOIN employees_table t2 ON t1.employee_id = t2.employee_id AND t2.num = 2
JOIN employees_table t3 ON t1.employee_id = t3.employee_id AND t3.num = 3
JOIN employees e1 ON t1.employee_id = e1.employee_id
-- 3. 엄격한 상승 조건 (최신 1번 > 2번 > 3번) 필터링
WHERE t1.rating > t2.rating AND t2.rating > t3.rating
ORDER BY improvement_score DESC, name ASC;

• WHERE절에서 대소 비교 필터링: 3번의 평가에 걸쳐 매번 점수가 향상한 데이터만 WHERE 조건절에서 걸러낸다. 조인 조건절에서는 최신, 직전, 그전 데이터를 정확하게 명시하여 가로로 연결한 후, 대소 비교는 WHERE절에서 담당한다. 

---

1. 원본 데이터 (employees_table)

employee_id가 101인 사원의 리뷰가 3개 있다고 가정한다.

employee_id	rating	num
101	90	1 (최신)
101	80	2 (직전)
101	70	3 (그전)

---

2. JOIN 후의 가상 표 (조립 과정)

작성하신 JOIN 쿼리가 실행되면, 같은 employee_id를 가진 행들이 num 조건에 맞춰 한 줄로 쭉 늘어선다.

t1.emp_id	t1.rating (num=1)	t2.emp_id	t2.rating (num=2)	t3.emp_id	t3.rating (num=3)	e1.name
101	90	101	80	101	70	"Kim"

---

3. 필터링 및 계산 (WHERE & SELECT)

이제 이 길어진 한 줄을 가지고 컴퓨터가 검사를 시작합니다.

1. 조건 검사 (WHERE):
   • t1.rating(90) > t2.rating(80)? → Yes
   • t2.rating(80) > t3.rating(70)? → Yes
   • (모든 조건을 만족하므로 이 행은 탈락하지 않고 살아남습니다.)
2. 값 계산 (SELECT):
   • improvement_score: t1.rating(90) - t3.rating(70) = 20

---

4. 최종 결과물

사용자님이 보게 될 최종 표는 필요한 컬럼만 남긴 아래의 형태가 됩니다.

employee_id	name	improvement_score
101	"Kim"	20