[SQL][HackerRank] Contest Leaderboard - INNER JOIN + SUBQUERY + MAX + GROUP BY ORACLE

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

Contest Leaderboard | HackerRank

코드설명

INNER JOIN + SUBQUERY + MAX + GROUP BY 를 활용합니다.

 

1. 각 SUBMISSON 당 HACKER_ID, CHALLENGE_ID 별로 최대 점수를 구합니다.

SELECT HACKER_ID, CHALLENGE_ID, MAX(SCORE) AS SCORE
    FROM SUBMISSIONS
    GROUP BY HACKER_ID, CHALLENGE_ID

 

2. 1번과정에서 구한 데이터들과 HACKER 테이블을 결합하고, 해당 데이터들의 총 합을 구합니다.

SELECT H.HACKER_ID, H.NAME, SUM(MAX_SCORES.SCORE) AS TOTAL_SCORE
FROM HACKERS H
INNER JOIN (
    SELECT HACKER_ID, CHALLENGE_ID, MAX(SCORE) AS SCORE
    FROM SUBMISSIONS
    GROUP BY HACKER_ID, CHALLENGE_ID
) MAX_SCORES
ON H.HACKER_ID = MAX_SCORES.HACKER_ID
GROUP BY H.HACKER_ID, H.NAME

 

3. SUM이 0보다 커야하고, 정렬조건을 붙여줍니다.

SELECT H.HACKER_ID, H.NAME, SUM(MAX_SCORES.SCORE) AS TOTAL_SCORE
FROM HACKERS H
INNER JOIN (
    SELECT HACKER_ID, CHALLENGE_ID, MAX(SCORE) AS SCORE
    FROM SUBMISSIONS
    GROUP BY HACKER_ID, CHALLENGE_ID
) MAX_SCORES
ON H.HACKER_ID = MAX_SCORES.HACKER_ID
GROUP BY H.HACKER_ID, H.NAME
HAVING SUM(MAX_SCORES.SCORE) > 0
ORDER BY TOTAL_SCORE DESC, H.HACKER_ID ASC;

ORACLE 코드

SELECT H.HACKER_ID, H.NAME, SUM(MAX_SCORES.SCORE) AS TOTAL_SCORE
FROM HACKERS H
INNER JOIN (
    SELECT HACKER_ID, CHALLENGE_ID, MAX(SCORE) AS SCORE
    FROM SUBMISSIONS
    GROUP BY HACKER_ID, CHALLENGE_ID
) MAX_SCORES
ON H.HACKER_ID = MAX_SCORES.HACKER_ID
GROUP BY H.HACKER_ID, H.NAME
HAVING SUM(MAX_SCORES.SCORE) > 0
ORDER BY TOTAL_SCORE DESC, H.HACKER_ID ASC;