[MySQL] HackerRank : Top Competitors Solution

https://www.hackerrank.com/challenges/full-score/problem

Top Competitors | HackerRank

 

문제 접근 방식

Submissions 테이블과 Challenges 테이블을 조인하여 각 챌린지에 대한 difficulty_level 값을 가져온다.

가져온 difficuly_level 컬럼을 이용하여 Difficulty 테이블과 조인하여 문제해서 의미하는 full score 값인 Diffuculty 테이블의 score 값을 가져온다. 이 테이블에서 score 값과 full score 값이 같은 튜플들을 필터링하면 챌린지에서 full score을 맞은 해커 id 값을 가져올 수 있다. 

마찬가지로 Submission 테이블과 Hackers 테이블을 hacker_id를 통해 조인하면 id에 대한 name 값도 알아낼 수 있다. 

 

 

 

(1) 에러가 발생한 코드 

SELECT S.hacker_id, H.name
              FROM Submissions S
              INNER JOIN Hackers H ON H.hacker_id = S.hacker_id
              INNER JOIN Challenges C ON S.hacker_id = C.hacker_id  # 문제 1 
              INNER JOIN Difficulty D ON C.difficulty_level = D.difficulty_level
     GROUP BY S.hacker_id, H.name
     HAVING S.score = D.score # 문제 2

[#문제1]

Challenges 테이블의 프라임 키인 chaellenge_id를 이용하여 Submissions 테이블과 조인 시켜줘야 했는데 hacker_id를 사용하는 바람에 오류가 발생하였다.

hacker_id를 사용하여 조인을 하면 안되는 이유는 위 예시를 보면 알 수 있다. hacker_id가 77726인 참가자가 두개의 challenge에 참여하는데, 이때 각 challenge에 대한 difficulty_level이 필요하기 때문이다. 

 

예를 들어, 다음과 같은 쿼리를 사용하면 

SELECT S.hacker_id, S.challenge_id, C.difficulty_level

              FROM Submissions S
              LEFT JOIN Challenges C ON S.hacker_id = C.hacker_id

 

결과값으로 아래와 같은 값이 출력된다. 

challenge_id가 서로 다르므로 difficulty_level도 다르게 출력되어야 하는데, 모두 하나의 값으로 세팅된 것을 확인할 수 있다. 

 

[#문제2]

ERROR 1054 (42S22) at line 1: Unknown column 'S.score' in 'having clause'

 

score 점수와 full score 점수가 같은 컬럼명들을 모아 full score를 두번 이상 받은 hacker_id와 name을 추출하고자 GROUP BY와 HAVING을 이용하였다. 

GROUP BY절보다 WHERE절이 먼저 동작하기 때문에 우선 WHERE 절을 통해 score 점수가 full score 점수와 일치하는 컬럼들을 필터링 해야한다. 

 

SELECT S.hacker_id, H.name, S.score, D.score 
FROM Submissions S INNER JOIN Hackers H ON H.hacker_id = S.hacker_id 
INNER JOIN Challenges C ON S.challenge_id = C.challenge_id
INNER JOIN Difficulty D ON C.difficulty_level = D.difficulty_level 
GROUP BY S.hacker_id, H.name, S.score, D.score 
HAVING S.score = D.score

 

이러한 방식으로 컬럼명과 GROUP BY 절에 해당 S.score과 D.score를 넣으면 에러가 사라지긴 한다.

하지만 우리가 조회하고 싶은 컬럼은 hacker_id와 name 두 가지뿐이므로 where절을 사용하여 필터링해주는 방법으로 접근해야 한다. 

 

 

(2) 에러가 발생한 코드  

SELECT S.hacker_id, H.name
              FROM Submissions S
              INNER JOIN Hackers H ON H.hacker_id = S.hacker_id
              INNER JOIN Challenges C ON S.challenge_id = C.challenge_id
              LEFT JOIN Difficulty D ON C.difficulty_level = D.difficulty_level
     WHERE S.score = D.score
     GROUP BY S.hacker_id, H.name
     HAVING COUNT(S.hacker_id) >= 2 
     ORDER BY COUNT(S.hacker_id) DESC, S.hacker_id # 문제 1

 

[#문제1] 

또한 문제에서 full score를 획득한 횟수가 많은 순으로 내림차순 정렬한 후, 획득한 개수가 같다면 hacker_id를 오름차순으로 정렬하라고 하였다. 따라서 submission_id 와 같은 값으로 획득한 횟수에 대한 카운팅을 해주어야 한다. 

 

(2) 정답인 코드

SELECT S.hacker_id, H.name
              FROM Submissions S
              INNER JOIN Hackers H ON H.hacker_id = S.hacker_id
              INNER JOIN Challenges C ON S.challenge_id = C.challenge_id
              INNER JOIN Difficulty D ON C.difficulty_level = D.difficulty_level
     WHERE S.score = D.score
     GROUP BY S.hacker_id, H.name
     HAVING COUNT(DISTINCT S.submission_id) >= 2 
     ORDER BY COUNT(DISTINCT S.submission_id) DESC, S.hacker_id