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

 

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

핵심은 각 해커가 각 챌린지에서 받은 최고 점수만 합산하여 총 점수를 계산하는 것입니다.

SELECT 
    h.hacker_id,
    h.name,
    SUM(t.max_score) AS total_score
FROM 
    Hackers h
JOIN (
    SELECT 
        hacker_id,
        challenge_id,
        MAX(score) AS max_score
    FROM Submissions
    GROUP BY hacker_id, challenge_id
) t ON h.hacker_id = t.hacker_id
GROUP BY h.hacker_id, h.name
HAVING SUM(t.max_score) > 0
ORDER BY total_score DESC, h.hacker_id ASC;

• MAX(score): 챌린지별 최고 점수만 인정!
• GROUP BY hacker_id, challenge_id: 해커별로 챌린지별 최고 점수 계산.
• HAVING SUM > 0: 점수가 0이면 제외.
• ORDER BY: 점수 내림차순, 동점이면 해커 ID 오름차순.