[해커랭크/SQL] 38. Contest Leaderboard

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

Contest Leaderboard | HackerRank

 

---

🤔 문제 분석

1. You did such a great job helping Julia with her last coding contest challenge that she wants you to work on this one, too!

2. The total score of a hacker is the sum of their maximum scores for all of the challenges.

3. Write a query to print the hacker_id, name, and total score of the hackers ordered by the descending score.

4. 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 hacker_id, name, SUM(score) AS score
FROM(
    SELECT h.hacker_id, h.name, challenge_id, max(score) AS score
    FROM Submissions AS s
        INNER JOIN Hackers AS h on s.hacker_id = h.hacker_id
    GROUP BY hacker_id, name, challenge_id
    ) AS sub
GROUP BY hacker_id, name
HAVING score <> 0
ORDER BY score DESC, hacker_id