[SQL] HackerRank - Top Competitors 문제풀이

🔊 본 포스팅에서 사용되는 테이블의 자료와 출처는 HackerRank 임을 밝힙니다. 더 다양한 SQL 문제를 풀어보시려면

HackerRank 사이트를 방문해 보세요!

 

HackerRank 사이트

 

이번 포스팅에서는 해커랭크 사이트의 Top Competitors 문제를 풀이해보자. 문제 원본은 여기를 클릭해 읽어보자.

문제에서 주어진 테이블들은 문제 원본을 살펴보고 여기에서는 어떤 출력의 내용은 요구하는지에 대해서 이해해보자.

 

Julia just finished conducting a coding contest, and she needs your help assembling the leaderboard! Write a query to print the respectivehacker_idandnameof hackers who achieved full scores formore than onechallenge. Order your output in descending order by the total number of challenges in which the hacker earned a full score. If more than one hacker received full scores in same number of challenges, then sort them by ascendinghacker_id.
(줄리아는 코딩 대회를 마쳤고 참가자들의 점수 리더보드를 만드려고 한다. 2개 이상의 Challenge(문제)에서 Full score(만점)를 받은 Hacker(참가자)들의 이름과 hacker_id를 출력해라. 이 때 결과 화면을 Full score를 받은 Challenge의 개수를 기준으로 내림차순 정렬하고 만약 Full score를 받은 Challenge 개수가 동일하다면 hacker_id 를 기준으로 오름차순 정렬해라.)

 

이제 우리가 해야 하는 일은 다음과 같다.

 

1. Full score(만점)을 받은 챌린지가 2개 이상인 참가자들을 찾자.
2. 이 때 만점이란, Submissions 테이블의 score 값과 Difficulty 테이블의 score 값과 일치하는 것을 의미
3. 2가지 정렬 기준을 만족해서 출력시키기

우리는 이를 해결하기 위해 지난 포스팅에서 했던 것처럼 연속적인 JOIN 구문을 사용해서 해결할 것이다. 먼저 정답 SQL 구문을 살펴보자.

 

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

 

이제 INNER JOIN 구문 하나를 예시로 살펴보자. 우선 3번째 라인의 Submissions 테이블과 Challenges 테이블을 INNER JOIN 하게 되면 다음과 같아진다.(테이블 값들의 예시는 필자가 임의로 만든 것임을 참고하자)

 

submission_id	hacker_id (문제 풀이한hacker_id)	challenge_id	score(맞은 점수)	challenge_id	hacker_id (문제를 만든 hacker_id)	difficulty_level
1번	철수	3번 문제	60점	3번 문제	다솜	5
2번	기영	9번 문제	100점	9번 문제	유리	7
3번	다솜	12번 문제	30점	12번 문제	기영	3

 

 

위 조인시킨 테이블을 보았을 때, 포인트 부분은 2개의 hacker_id 값이 있는데 각자 의미하는 바가 다르다는 것이다. 즉, challenge_id 값은 동일하지만 그 challenge를 누가 냈고 누가 풀이했는지는 다르다는 것이다! 위 테이블을 보면 다솜이는 3번 문제를 만들기도 했지만 12번 문제를 풀기도 했음을 알 수 있다.

 

이제 위와 같은 방식으로 연속적으로 Difficulty 테이블과 Hackers 테이블을 연속적으로 INNER JOIN 시켜주자.

 

이제 WHERE 구문절에도 써있다시피 Full score를 맞은 참가자들만 필터링시켜주어야 하는데, 조건을 2개로 달아준다. 즉, 문제의 만점 기준 점수와 참가자가 풀고 난 점수가 동일하면서 문제의 난이도도 동일해야 한다.

 

그리고 Full score를 맞은 문제가 2개 이상인 사람들을 알아보기 위해 hacker_id, name을 기준으로 그룹핑을 시켜주고 hakcer_id를 COUNT해준다. 이 때 COUNT(hacker_id) 한 값이 1 보다 클 때 바로 Full score를 맞은 문제가 2개 이상인 hacker_id, name만 남게 된다.

 

마지막으로 COUNT(hacker_id)해준 값을 최우선 정렬 기준으로 사용해주고 동일한 COUNT(hacker_id)값을 가질 때는 hacker_id 기준으로 정렬해주도록 하자.