[SQL] HackerRank 문제 풀기 (Contest Leaderboard)

해당 문제는 HackerRank에서 사용한 문제이며 모든 테이블의 자료와 출처는 Hackerank임을 밝힙니다.

해커랭크

해당 글에서는 서브쿼리를 사용하여 푸는 문제로 난이도가 매우 낮은 문제입니다.

해당 문제 원본을 확인 하시려면 해당 사이트를 들어가시면 될 것 같습니다.

 

시작하겠습니다

 

You did such a great job helping Julia with her last coding contest challenge that she wants you to work on this one, too!
The total score of a hacker is the sum of their maximum scores for all of the challenges. Write a query to print the hacker_id, name, and total score of the hackers ordered by the descending score. If more than one hacker achieved the same total score, then sort the result by ascending hacker_id. Exclude all hackers with a total score of 0 from your result.

해커의 총 점수는 모든 도전 과제에서의 최고 점수 합계입니다. 쿼리를 작성하여 해커의hacker_id, 이름(name), 그리고 총 점수(total score)를 출력하세요. 결과는 점수를 기준으로 내림차순으로 정렬하세요. 만약 여러 해커가 동한 총 점수를 달성했다면,hacker_id를 기준으로 오름차순으로 정렬하세요. 총 점수가 0인 해커는 결과에서 제외해야 합니다.

해당 문제는 WINDOW  함수(ROW_NUMBER)와 GROUP BY를 활용하여 풀어보겠습니다.

우선, ROW_NUMBER를 통해 해커ID와 챌린지ID를 파티션으로 분할한 뒤 높은 Score 순으로 순위를 주고 시작 할 것입니다. 그 후, 1위인 순위들만 가지고 계산에 적용할 것입니다.

 

1. ROW_NUMBER 활용하여 순위 부여

select *
     , ROW_NUMBER() OVER (PARTITION BY hacker_id, challenge_id ORDER BY score DESC) rank
from Submissions

코드 결과

위의 이미지와 같이 해커가 20594라는 시험을 여러번 봤으며 여러번 본 시험에서의 높은 점수 순으로 순위를 부여 하였습니다.

이제 해당 데이터를 가상테이블로 만들어 주고 코드를 이어서 작성하겠습니다.

---

2. 제일 높은 점수로만 각 해커의 점수 구하기

WITH CTE AS (
    select *
         , ROW_NUMBER() OVER (PARTITION BY hacker_id, challenge_id ORDER BY score DESC) rank
    from Submissions
)

SELECT a.hacker_id, b.name, SUM(a.score) total
FROM CTE a
JOIN Hackers b ON a.hacker_id = b.hacker_id
WHERE rank = 1
GROUP BY a.hacker_id, b.name

코드 결과

이렇게 name을 가져오기 위해서 Hackers 테이블을 조인 해주었습니다.

저희는 각 해커가 풀었던 문제 중 제일 높은 점수만 원하기 때문에 WHERE절을 통해 1순위만 가져와주었습니다.

그리고 해커ID와 이름을 기준으로 GROUP BY를 해준뒤 score를 모두 더해주었습니다.

---

3. 총점이 0 제외 시키고 정렬해주기

WITH CTE AS (
    select *
         , ROW_NUMBER() OVER (PARTITION BY hacker_id, challenge_id ORDER BY score DESC) rank
    from Submissions
)

SELECT a.hacker_id, b.name, SUM(a.score) total
FROM CTE a
JOIN Hackers b ON a.hacker_id = b.hacker_id
WHERE rank = 1
GROUP BY a.hacker_id, b.name
HAVING SUM(a.score) != 0
ORDER BY total DESC, a.hacker_id ASC

WHERE 절에는 모든 총합의 점수가 0인 것은 확인 할 수 없으므로 HAVING 절을 사용하여 총점이 0인 값은 제외하고 출력하도록 해주었습니다.

마지막으로 총점과 해커ID를 정렬해주면 정답을 확인 할 수 있습니다.

---

이상으로 Hacker Rank 문제 풀이를 마치도록 하겠습니다.