링크

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

문제 설명

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 from your result.

코드

SELECT hack.hacker_id, hack.name, SUM(mx.max_score) AS total_sum 
FROM (
    SELECT hacker_id, MAX(score) AS max_score FROM Submissions
    GROUP BY challenge_id,hacker_id) AS mx    
INNER JOIN Hackers hack 
ON mx.hacker_id=hack.hacker_id
GROUP BY mx.hacker_id, hack.name
HAVING total_sum>0
ORDER BY total_sum DESC, hack.hacker_id ASC;