[해커랭크]Contest Leaderboard

문제

https://www.hackerrank.com/challenges/contest-leaderboard/problem?isFullScreen=true

코드

SELECT
    hacker_id,
    ANY_VALUE(name) AS name,
    SUM(max_score) AS total_score
FROM 
    (SELECT
        sub.hacker_id,
        ANY_VALUE(hac.name) AS name,
        MAX(sub.score) AS max_score
    FROM
        Hackers hac
        INNER JOIN Submissions sub ON hac.hacker_id = sub.hacker_id
    GROUP BY
        sub.hacker_id,
        sub.challenge_id) MAX_SCORE
GROUP BY 
    hacker_id
HAVING 1=1
    AND total_score != 0
ORDER BY
    total_score DESC,
    hacker_id

후기

아니 왜 CTE 안되냐...
그리고 그놈의 only_full_group_by 때문에 ANY_VALUER() 함수를 써 줘야 됨.;;;
only_full_group_by : GROUP BY 절 이외에는 모두 집계 함수를 써야 되는 모드.
ANY_VALUER(): 그룹화 후 집계처리 안하고 출력하도록 하는 함수