[Leetcode] Find Students Who Improved

문제 링크

정답

WITH condition1 AS (
    SELECT 
        student_id, subject,
        MIN(exam_date) first_date,
        MAX(exam_date) latest_date
    FROM Scores
    GROUP BY student_id, subject
    HAVING COUNT(*) > 1 -- 조건 1: 과목 당 시험 2회 이상
)

SELECT *
FROM (
    SELECT 
        T.student_id,
        T.subject,
        SUM(IF(T.first_date = S.exam_date, S.score, 0)) as first_score,
        SUM(IF(T.latest_date = S.exam_date, S.score, 0)) as latest_score
    FROM condition1 T
    JOIN Scores S
    ON T.student_id = S.student_id
    AND T.subject = S.subject
    GROUP BY T.student_id, T.subject
) T
WHERE T.first_score < T.latest_score
ORDER BY T.student_id, T.subject

✅ Point

• 조건 1: 동일한 과목에 대하여 시험을 2회 이상 본 학생만
  • GROUP BY student_id, subject
  • HAVING COUNT(*) > 1
  • 첫 점수, 마지막 점수를 구하기 위해 이를 구분해줄 수 있는 첫 시험 날짜, 마지막 시험 날짜도 SELECT에 포함시킴
• 조건 2: 첫 점수와 마지막 점수 비교
  • JOIN 으로 Scores table 연결
  • IF(exam_date = first_date, score, 0) -> 첫 점수
  • IF(exam_date = latest_date, score, 0) -> 마지막 점수
  • SUM으로 first_score 와 latest_score을 한 행에 표현하기
• 조건 3: 점수가 오른 경우만 필터링
  • WHERE first_score < latest_score
• 조건 4:
  • 정렬

다른 풀이

• CTE + Window 함수를 이용한 풀이
• CTE 를 사용하는 이유
  • 학생별, 과목별로 첫 점수, 마지막 점수, 시험 횟수를 추출해두기 위해서
• DISTINCT를 사용하는 이유
  • GROUP BY를 사용하면 한 행에 첫 점수, 마지막 점수를 한 번에 가져올 수 없음
  • 불필요한 중복을 제거하기 위해 DISTINCT 사용
• FIRST_VALUE, LAST_VALUE 사용 시 주의점
  • 해당 WINDOW 함수가 적용되는 기준은 처음 행부터 현재 행까지
  • 현재 행 이후의 행도 모두 보기 위하여 추가적인 범위 명시: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
• 최종 SELECT에서 조건 필터링
  • 시험 횟수 2회 이상
  • 첫 점수 < 마지막 점수

WITH CTE AS (
    SELECT 
        DISTINCT student_id, subject,
        FIRST_VALUE(score) OVER(
            PARTITION BY student_id, subject 
            ORDER BY exam_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) as first_score,
        LAST_VALUE(score) OVER(
            PARTITION BY student_id, subject 
            ORDER BY exam_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 
        as latest_score,
        COUNT(student_id) OVER(PARTITION BY student_id, subject) cnt
    FROM Scores

)
SELECT 
    student_id, 
    subject, 
    first_score, 
    latest_score 
FROM CTE WHERE cnt > 1 
AND first_score < latest_score
ORDER BY 1, 2;