문제

https://www.hackerrank.com/challenges/contest-leaderboard/problem

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.

풀이

SELECT Hackers.hacker_id
	, name
    , SUM(max) sum
FROM Hackers
    INNER JOIN (
        SELECT hacker_id
        	, challenge_id
            , MAX(score) max
        FROM Submissions
        GROUP BY hacker_id, challenge_id
    ) max_id ON Hackers.hacker_id = max_id.hacker_id
GROUP BY Hackers.hacker_id, name
HAVING sum > 0
ORDER BY sum DESC, hacker_id