[Hacker Rank_MySQL]문제 풀이#Contest Leaderboard

SQL > Basic Join > Contest Leaderboard

Contest Leaderboard | HackerRank

 

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.

 

빨간 글씨는 내가 문제를 풀 때 중요하다고 생각한 부분이다.

 

SELECT H.HACKER_ID, H.NAME, SUM(TS.MS)
FROM HACKERS H
JOIN (SELECT HACKER_ID, CHALLENGE_ID, MAX(SCORE) MS
      FROM SUBMISSIONS
      GROUP BY HACKER_ID, CHALLENGE_ID) TS
ON H.HACKER_ID = TS.HACKER_ID
GROUP BY H.HACKER_ID, H.NAME
HAVING SUM(TS.MS) != 0
ORDER BY SUM(TS.MS) DESC, H.HACKER_ID ASC