Contest Leaderboard / HackerRank SQL MySQL

문제

source: 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.

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

 

 

 

제출답안(MySQL)

SELECT hacker_id, name, SUM(max_score) AS total_score
FROM
    ( SELECT s.hacker_id, h.name, s.challenge_id, MAX(s.score) as max_score
     FROM submissions AS s JOIN hackers AS h ON s.hacker_id = h.hacker_id
     GROUP BY hacker_id, name, challenge_id
    ) j_sh
GROUP BY hacker_id, name
HAVING total_score > 0
ORDER BY 3 DESC, 1

 

 

 

풀이(MySQL)

 

최종적으로 join 한 테이블에서 hacker_id, name, 각 챌린지의 최대 점수 합을 조회

SELECT hacker_id, name, SUM(max_score) AS total_score

 

submissions와 hackers 테이블 조인 후 최종쿼리에 필요한 정보 및 challenge 별 최고점수 조회

FROM
    ( SELECT s.hacker_id, h.name, s.challenge_id, MAX(s.score) as max_score
     FROM submissions AS s JOIN hackers AS h ON s.hacker_id = h.hacker_id
     GROUP BY hacker_id, name, challenge_id
    ) j_sh

 

해커의 id와 이름으로 그룹화 

GROUP BY hacker_id, name

 

GROUP BY 결과에서 총 점수가 0점 넘는 데이터만 조회 및 정렬

HAVING total_score > 0
ORDER BY 3 DESC, 1

 

 

 

 

 

 

 

 

문제 바로가기(MySQL)