본 내용은 데이터리안 '데이터 분석을 위한 고급 SQL 문제풀이'을 수강하며 작성한 내용입니다.
문제
https://www.hackerrank.com/challenges/contest-leaderboard/problem?isFullScreen=true
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.
풀이
- 나의 풀이과정
SELECT *
FROM Submissions AS A
LEFT JOIN Hackers AS B ON A.hacker_id = B.hacker_id- 선생님 풀이과정
SELECT h.hacker_id
, h.name
, SUM(score_max) AS total_score
FROM (
SELECT hacker_id
, challenge_id
, MAX(score) AS score_max
FROM Submissions
GROUP BY hacker_id, challenge_id
) AS t INNER JOIN Hackers AS h ON h.hacker_id = t.hacker_id
GROUP BY h.hacker_id, h.name
HAVING total_score != 0
ORDER BY total_score DESC, h.hacker_id배운점
- 서브쿼리 테이블을 바로 INNER JOIN과 연결해서 쓸 수 있다. () AS t INNER JOIN~ 이런 식으로.
- 같은 사람이 여러개의 챌린지에서 여러개의 점수를 가진 경우, hacker_id와 name을 GROUP BY로 묶어서, 점수를 연산해줄 수 있다. ex. GROUP BY h.hacker_id, h.name
