Contest Leaderboard

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 M.id, M.name, SUM(M.total)
FROM
(SELECT MAX(S.score) total, S.hacker_id id, H.name name
    FROM Submissions S, Hackers H
    WHERE S.hacker_id = H.hacker_id
    GROUP BY S.challenge_id, S.hacker_id, H.name) M
HAVING SUM(M.total) != 0
GROUP BY M.id, M.name
ORDER BY SUM(M.total) desc, M.id;