출처
: 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 $0$ from your result.
나의 풀이
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT SEP_2.SEP_HID AS FIN_HID, H.NAME, SEP_2.SEP_SUM_MAX AS FIN_SCORE
FROM (
SELECT SEP.HID AS SEP_HID, SUM(SEP.MAX_SCORE) AS SEP_SUM_MAX
FROM (
SELECT S.HACKER_ID AS HID,
S.CHALLENGE_ID AS CID,
MAX(SCORE) AS MAX_SCORE
FROM SUBMISSIONS S
GROUP BY S.HACKER_ID, S.CHALLENGE_ID
) SEP
GROUP BY SEP.HID
) SEP_2
INNER JOIN HACKERS H
ON SEP_2.SEP_HID = H.HACKER_ID
WHERE SEP_2.SEP_SUM_MAX <> 0
ORDER BY FIN_SCORE DESC, FIN_HID ASC;