LeetCode - 3421. Find Students Who Improved (Oracle)

Medium - SQL, CTE, Partition

Runtime : 416 ms

문제

Write a solution to find the students who have shown improvement. A student is considered to have shown improvement if they meet both of these conditions:

• Have taken exams in the same subject on at least two different dates
• Their latest score in that subject is higher than their first score

Return the result table ordered by student_id, subject in ascending order.

The result format is in the following example.

풀이

• exam_date를 기준으로 가장 최신, 나중 값을 가져온다.

  • latest_score를 뽑아올 때, LAST_VALUE()를 쓰고 싶다면?

• LAST_VALUE()의 경우, ORDER_BY 이후,
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING이 필요함.

  • 기본 윈도우 프레임의 마지막행(현재)를 반환하므로
    현재 행 값이 매번 등장하기 때문

• 이후에 WHERE 조건에 맞는 값들만 중복을 제거해 가져온다.

with ranked as (
    select  student_id,
            subject,
            first_value(score) over 
			(partition by student_id, subject order by exam_date) as first_score,
            first_value(score) over
			(partition by student_id, subject order by exam_date desc) as latest_score
    from scores
)

select  	distinct *
from    	ranked
where   	first_score < latest_score
order by 	student_id, subject;

최근 배웠던 CTE, FIRST_VALUE, LAST_VALUE를 모두 접할 수 있던 문제.