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

📔문제

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.

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.

---

📝예시

Hackers Table:

Challenges Table:

4071 Rose 191
74842 Lisa 174
84072 Bonnie 100
4806 Angela 89
26071 Frank 85
80305 Kimberly 67
49438 Patrick 43

---

🧮분야

• JOIN

---

📃SQL 코드

select h.hacker_id, name, sum(m) total
from hackers h, (select hacker_id, challenge_id, max(score) m
                from submissions
                group by hacker_id, challenge_id) s
where h.hacker_id=s.hacker_id
group by h.hacker_id, name
having sum(m)>0
order by total desc, hacker_id;

---

📰출력 결과

---

📂고찰

각 사용자, 챌린지 별로 최고점수만 뽑아서 그 최고점수들 끼리 합치면 된다.

그리고 hacker_id, name별로 묶어서 그 사용자의 sum(최고점수들)로 하면 되는데, 이 때 최고점수 중 0점만 있는 것은 제외하면 된다.