[HackerRank] Contest Leaderboard

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

Contest Leaderboard | HackerRank

1. hacker_id별로 challenge_id score가 여러개일 경우 가장 큰 점수만 선택하여

  각각의 challenge_id score 점수 합하기

2. total_score 역순, 동점자가 있으면 hacker_id 순으로 정렬

3. total_score가 0점인 것들은 제외하기

 

SELECT hacker_id
    , name
    , SUM(max_score) total_score
FROM (SELECT s.hacker_id
        , name
        , challenge_id
        , MAX(score) max_score
    FROM Submissions s JOIN Hackers h
        ON s.hacker_id = h.hacker_id
    GROUP BY s.hacker_id, name, challenge_id) A
GROUP BY hacker_id, name
HAVING SUM(max_score) > 0
ORDER BY 3 DESC, 1