Top Competitors

1. Top Competitors

• 난이도
  • Medium
• 출처
  • Top Competitors | HackerRank

A. 📜 문제

코딩 대회의 리더보드를 만들어야한다. 쿼리를 작성하여 하나보다 많은 챌린지에 만점을 획득한 각 hacker_id, name을 출력하라.

해커(player)가 만점을 획득한 총 문제 수에 따라 출력을 내림차순으로 정렬한다. 둘 이상의 해커가 동일한 수의 챌린지에서 만점을 받은 경우 오름차순으로 hacker_id를 정렬한다.

B. 💡 내 답안

a. 😊 1차 시도 (성공)

SELECT H.hacker_id,
       H.name
FROM (
    SELECT DISTINCT(S.hacker_id) AS hacker_id,
           COUNT(*) OVER (PARTITION BY S.hacker_id) AS id_count
    FROM Submissions S
        LEFT OUTER JOIN (
            SELECT C.challenge_id AS C_id,
                   D.score AS max_score
            FROM Challenges C
                LEFT OUTER JOIN Difficulty D
                    ON (D.difficulty_level = C.difficulty_level)
        ) NC
            ON (S.challenge_id = NC.C_id)
    WHERE S.score = NC.max_score
) NH
    LEFT OUTER JOIN Hackers H
        ON (NH.hacker_id = H.hacker_id)
WHERE NH.id_count >= 2
ORDER BY id_count DESC, hacker_id ASC
;

b. 😅 실패 케이스

SELECT h.hacker_id,
    h.name,
    cnt
FROM (
    SELECT s.hacker_id AS id,
        COUNT(s.hacker_id) AS cnt
    FROM submissions s
        LEFT OUTER JOIN (
            SELECT c.challenge_id AS chal_id,
                d.score AS max_score
            FROM challenges c
                LEFT OUTER JOIN difficulty d
                ON (c.difficulty_level = d.difficulty_level)
        ) challenge_score
        ON (s.challenge_id = challenge_score.chal_id)
    WHERE s.score = challenge_score.max_score
    GROUP BY s.hacker_id
    HAVING COUNT(s.hacker_id) >= 2
    ORDER BY cnt DESC, id ASC
) full_score_hacker LEFT OUTER JOIN hackers h
    ON (full_score_hacker.id = h.hacker_id)
;

order by를 인라인 뷰 안에서만 진행해주어서 최종 결과 테이블은 정렬되지 않은 상태로 출력된다.

c. 🙄 회고

내 풀이

• 최소단위 서브쿼리를 상상하며 관계를 확장해나갔다.

C. 🧐 문제 해설

이해한 내용을 바탕으로 작성했습니다.

우선 각 테이블 사이의 참조 관계는 아래와 같다.

따라서, difficulty_level 칼럼을 기준으로 Challenges 테이블을 고정하고 Difficulty 테이블을 Left outer join하여 새로운 테이블 NC를 만든다. 여기서 Left outer join은 join으로 변경해도 된다.
그리고 NC 테이블에서 Submissions 테이블과 조인하는데 필요한 컬럼(challenge_id, score)만 추출한다.
우리가 최종적으로 원하는 것은 만점 받은 회원이기 때문에 hacker_id(문제 제작자 ID)와 difficulty_level(문제 난이도)는 필요 없다.

challenge_id를 기준으로 Submissions 테이블과 NC 테이블을 조인하여 NH 테이블을 만든다.
여기에서는 Submissions 테이블의 score와 NC 테이블의 score가 같은 경우만 추출한다.
왜냐하면, 문제의 조건이 만점을 받은 사람만 추출하는 것이기 때문이다.
또한, 만점을 2개 이상 받은 경우만 출력해야하기 때문에 Window 함수를 사용하여 hacker_id의 count를 집계한다.
그리고 hacker_id는 중복이 있으니, DISTINCT로 중복제거를 한다.

최종으로 인라인 뷰에서 구한 hacker_id에 Hacker 테이블의 name 칼럼을 조인한다.
id_count가 2개 이상인 경우를 추출한다.
id_count로 내림차순 정렬을 하고, id_count가 같다면, hacker_id로 오름차순 정렬을 해준다.

뭔가 SQL은 깊게 들어가서 데이터를 정제하여 뽑아내는 작업같다. raw 데이터에 새로운 의미를 부여하는 느낌?.

참고문헌

Top Competitors | HackerRank. HackerRank. (accessed Jan 25, 2022)