[Hacker Rank] Contest Leaderboard

📢 본 포스팅에 활용되는 기본 문제 및 자료 출처는 HackerRank 임을 밝힙니다.

https://www.hackerrank.com

---

■ 문제

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 0 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.
   

Sample Input

Hackers Table: 

Submissions Table: 

Sample Output

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

Explanation

---

■ 풀이

문제 요구사항

> 총점(total score) = 해커별 그들의 challenges에 대한 최대 점수의 합

 

> 점수 내림차순으로 hacker_id, name, and total score of the hackers 추출

   (두명 이상의 해커가 총점이 똑같다면, hacker_id 순으로 정렬)

 

> 총점이 0인 해커들은 결과에서 제외할 것.

---

① hackers 테이블과 Submissions 테이블을 조인

     > 단, explanation에 따라 해커별로 동일한 challege에 대한 총점은 '최고점' 만 고려한다.

② Submissions 테이블에서 hacker_id, challenge_id별로 Max(score) max_score 을 추출

     ▲ 이 테이블과 Hackers 테이블을 inner join

           해커별 id, name, total_score( sum(s.max_sore))를 추출하는데

③ sum(s.max_score)가 0인 해커는 제외해야 하므로 집계함수 조건인 HAVING절로 조건 부여하고

④ 정렬 (order by ~)

 - (1순위) total_score가 높은 순

 - (2순위) total_score가 동일할 경우 id 오름차순으로 정렬

SELECT h.hacker_id, h.name, sum(s.max_score)
 FROM Hackers h
 JOIN (SELECT hacker_id, challenge_id, Max(score) max_score
        FROM Submissions 
        GROUP BY hacker_id, challenge_id
       ) s
 ON h.hacker_id = s.hacker_id
 GROUP BY 1,2
 HAVING SUM(s.max_score) > 0
 ORDER BY SUM(s.max_score) DESC, h.hacker_id;