[HackerRank SQL] Contest Leaderboard

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

Contest Leaderboard | HackerRank

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.

Input Format

The following tables contain contest data:

• Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker. 

  

• Submissions: The submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, challenge_id is the id of the challenge for which the submission belongs to, and score is the score of the submission. 

  

   

---

Answer

select h.hacker_id, h.name, sum(s.m_score)
from (select hacker_id, max(score) as m_score #The total score of a hacker is the sum of their maximum scores
    from submissions 
    group by hacker_id, challenge_id) as s
    left join
    hackers as h
    on s.hacker_id = h.hacker_id
group by h.hacker_id, h.name
having sum(s.m_score) != 0
order by sum(s.m_score) desc, h.hacker_id

먼저 hacker_id, challenge_id별로 score 최대값들을 뽑아낸뒤 이를 다시 h.hacker_id, h.name으로 그룹화하여 sum을 해준다. 그러면 최대값들을 다 합한 숫자가가 나오게 된다.

Result