YTN 면접 필기시험 쿼리 문제

각 과목 마다 점수가 있고, 해당 과목별 석차와 점수차를 구하는 문제
이걸 툴에서 짜면 이리 쉽게 짜는데, 막상 종이에 적으려니 생각 안나서, groub by, order by 만 쓰고 나왔으니..ㅠ

CREATE TABLE scores (
    subject VARCHAR(50),
    name VARCHAR(50),
    score INT
);

INSERT INTO scores (subject, name, score) VALUES
('수학', '김철수', 90),
('수학', '이영희', 85),
('수학', '박민수', 95),
('수학', '정지은', 80),
('국어', '김철수', 85),
('국어', '이영희', 90),
('국어', '박민수', 80),
('국어', '정지은', 95),
('영어', '김철수', 80),
('영어', '이영희', 95),
('영어', '박민수', 85),
('영어', '정지은', 90),
('과학', '김철수', 95),
('과학', '이영희', 80),
('과학', '박민수', 90),
('과학', '정지은', 85);

WITH ranked_scores AS (
    SELECT 
        subject,
        name,
        score,
        RANK() OVER (PARTITION BY subject ORDER BY score DESC) as rank,
        FIRST_VALUE(score) OVER (PARTITION BY subject ORDER BY score DESC) as top_score
    FROM 
        scores
)
SELECT 
    subject,
    name,
    score,
    rank,
    top_score - score as score_difference
FROM 
    ranked_scores
ORDER BY 
    subject,
    rank;

실행 결과:

subject	name	score	rank	score_difference
과학	김철수	95	1	0
과학	박민수	90	2	5
과학	정지은	85	3	10
과학	이영희	80	4	15
국어	정지은	95	1	0
국어	이영희	90	2	5
국어	김철수	85	3	10
국어	박민수	80	4	15
수학	박민수	95	1	0
수학	김철수	90	2	5
수학	이영희	85	3	10
수학	정지은	80	4	15
영어	이영희	95	1	0
영어	정지은	90	2	5
영어	박민수	85	3	10
영어	김철수	80	4	15

이 결과에서 볼 수 있듯이:

1. 각 과목별로 학생들의 점수가 정렬되어 있습니다.
2. 'rank' 열은 각 과목 내에서의 순위를 보여줍니다.
3. 'score_difference' 열은 해당 과목의 최고 점수와의 차이를 나타냅니다.

예를 들어, '과학' 과목에서:

• 김철수가 95점으로 1등이며, 1등과의 점수 차이는 0입니다.
• 박민수는 90점으로 2등이며, 1등과의 점수 차이는 5점입니다.

이 쿼리를 통해 각 과목별로 학생들의 성적 순위와 최고 점수와의 차이를 쉽게 확인할 수 있습니다.