[SQL] LeetCode 3421. Find Students Who Improved

문제 설명

• 성적이 향상된 학생을 찾는 문제

• 다음 두 조건이 만족하면 성적이 향상되었다고 판단한다.

  • 서로 다른 두 날짜에 같은 과목 시험을 치렀을 경우
  • 마지막 점수가 처음 점수보다 높은 경우

Input: Scores

student_id	subject	score	exam_date
101	Math	70	2023-01-15
101	Math	85	2023-02-15
101	Physics	65	2023-01-15
101	Physics	60	2023-02-15
102	Math	80	2023-01-15
102	Math	85	2023-02-15
103	Math	90	2023-01-15
104	Physics	75	2023-01-15
104	Physics	85	2023-02-15

Output

student_id	subject	first_score	latest_score
101	Math	70	85
102	Math	80	85
104	Physics	75	85

문제 상황

처음 문제를 접근 할 때 GROUP BY를 이용하여 student_id, subject 기준으로 접근해서 MIN(exam_date), MAX(exam_date)를 구했다.

하지만 이 방식만으로는 해당 날짜에 대응하는 score 값을 바로 알 수 없다는 한계가 있었다.
GROUP BY를 사용하면 집계 과정에서 개별 행 정보가 사라지기 때문에, 처음 시험일과 마지막 시험일에 실제로 기록된 점수를 얻기 위해서는 다시 Scores 테이블과 조인을 수행해야 했다.

이 과정에서 쿼리가 복잡해지고, “각 시험 기록을 순서대로 바라보면서 첫 번째 값과 마지막 값을 바로 비교할 수 없을까?”라는 의문이 들었다.

이 지점에서 행의 순서를 유지한 채 계산할 수 있는 윈도우 함수가 더 적합한 접근이라는 점을 깨달았다.

정답 코드

# Write your MySQL query statement below
WITH ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY student_id, subject ORDER BY exam_date) AS rn_asc,
        ROW_NUMBER() OVER (PARTITION BY student_id, subject ORDER BY exam_date DESC) AS rn_desc
    FROM
        Scores
)

SELECT 
    student_id,
    subject,
    MAX(CASE WHEN rn_asc = 1 THEN score END) AS first_score,
    MAX(CASE WHEN rn_desc = 1 THEN score END) AS latest_score
FROM
    ranked
GROUP BY
    student_id, 
    subject
HAVING
    first_score < latest_score

• ROW_NUMBER()를 이용해 student_id, subject를 파티션으로 나눈 뒤, exam_date 기준 오름차순(rn_asc), 내림차순(rn_desc) 순위를 각각 부여했다. 이를 통해 각 student_id, subject 조합에서 첫 번째 시험 행과 마지막 시험 행을 식별할 수 있다.

• 이후 CASE WHEN을 사용해 rn_asc = 1인 경우에는 first_score, rn_desc = 1인 경우에는 latest_score만 남기도록 했다.

SELECT 
    student_id,
    subject,
    CASE WHEN rn_asc = 1 THEN score END AS first_score,
    CASE WHEN rn_desc = 1 THEN score END AS latest_score
FROM ranked;

• 다만 이 쿼리를 그대로 실행하면, 각 그룹(student_id, subject) 내에서 첫 시험 행과 마지막 시험 행이 서로 다른 행으로 존재하기 때문에 아래와 같이 first_score와 latest_score가 각각 다른 행에 분산되어 출력된다.

student_id	subject	first_score	latest_score
101	Math	null	85
101	Math	70	null
101	Physics	null	60
101	Physics	65	null
102	Math	null	85
102	Math	80	null
103	Math	90	90
104	Physics	null	85
104	Physics	75	null

• 이 문제를 해결하기 위해 student_id, subject 기준으로 다시 한 번 그룹화한 뒤, MAX() 함수를 사용했다.

• 각 그룹 내에서 first_score와 latest_score는 각각 하나의 행에만 값이 존재하고 나머지는 NULL이므로, MAX()를 적용하면 NULL을 제외한 실제 점수 값만 선택할 수 있다.

• 그 결과, 한 행에서 처음 점수와 마지막 점수를 동시에 비교할 수 있는 형태로 정리할 수 있었다.