[HackerRank] Contest Leaderboard Solution (풀이)

Contest Leaderboard 문제 바로가기

Contest Leaderboard | HackerRank

---

문제

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.

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

1. 해커 아이디, 이름, 전체점수를 나타내는 쿼리 입력
2. 전체점수(total score) 은 해커가 푼 챌린지 중 가장 높은 점수끼리 더한 값 
    *한 해커가 동일한 챌린지를 여러 번 풀기도 함
3. 전체 점수가 0 인 경우는 제외하기 
4.  결과값은 전체 점수가 높은 순(내림차순) 정렬, 점수같으면 해커아이디 오름차순 정렬

---

 

풀이

서브쿼리로 문제풀이 

SELECT h.hacker_id, h.name, sum(s.score)
FROM (SELECT hacker_id , challenge_id , max(score) AS score
             FROM submissions
             GROUP BY hacker_id, challenge_id)  AS s
            JOIN hackers h ON h.hacker_id = s.hacker_id
GROUP BY h.hacker_id, h.name
HAVING sum(s.score) <> 0
ORDER BY sum(s.score) DESC , h.hacker_id