[TIL] MySQL TIL Day 3

🟡 [HackerRank] Contest Leaderboard

https://www.hackerrank.com/challenges/contest-leaderboard/problem?isFullScreen=true

Trial

이 문제는 서브쿼리를 사용해야 하는 문제이다. sql 초짜라 서브쿼리를 제대로 써본 적이 없어 난항을 겪었다..처음에는 GROUP BY로 Max와 Sum을 동시에 수행하려 했지만 (당연히) 실패했다. 서브쿼리는 쉽게 말하면 SQL문 안에 있는 또 다른 SQL 문이다. 서브쿼리에 대한 설명은 이 포스팅에 잘 정리되어 있어 참고하기 좋았다.

Solution

이 문제를 해결하기 위해서는 먼저 hacker_id, challenge_id로 그룹화하여 score의 max를 뽑는 서브쿼리를 생성한 후, 기존의 hacker table과 조인해야 한다.

Step 1

FROM 절에 서브쿼리를 생성한다. Submission table에서 hacker_id, challenge_id로 그룹화(Group by) 후 각 그룹별 max score를 추출한다. 해당 서브쿼리는 s로 명명한다.(ALIAS)

Step 2

Step 1에서 생성한 서브쿼리와 Hackers table를 JOIN 한다. 이 때 hacker_id를 기준으로 JOIN!

Step 3

이제 각 hacker_id 별 total score를 계산하기 위해 GROUP BY를 다시 한다. Output에서 hacker_id, name, total score 칼럼을 포함할 것이므로 GROUP BY hacker_id, name으로 지정해준다. 각 total_score은 SUM(score)을 이용하여 구할 수 있다.

Step 4

문제에서 제시한 조건에 따라 total score이 0보다 큰 데이터만 선택하고, total score 기준으로 내림차순 정렬, hacker_id 기준으로 오름차순 정렬한다.

SELECT h.hacker_id, h.name, SUM(score) AS total
FROM (SELECT hacker_id, challenge_id, MAX(score) AS score
      FROM submissions GROUP BY hacker_id, challenge_id) AS s
JOIN hackers AS h ON h.hacker_id = s.hacker_id
GROUP BY h.hacker_id, h.name
HAVING total > 0
ORDER BY total DESC, hacker_id