[SQL][HackerRank]Top Competitors

🔊본 포스팅에서 사용되는 테이블의 자료와 출처는 HackerRank 임을 밝힙니다.
https://www.hackerrank.com/challenges/full-score/problem?isFullScreen=false

---

🎈문제 풀이

Julia just finished conducting a coding contest, and she needs your help assembling the leaderboard! Write a query to print the respective hacker_id and name of hackers who achieved full scores for more than one challenge. 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 ascending hacker_id.

줄리아는 코딩 대회를 마쳤고 참가자들의 점수 리더보드를 만드려고 한다. 2개 이상의 Challenge(문제)에서 Full score(만점)를 받은 Hacker들의 이름과 hacker_id를 출력해라. 이 때 결과 화면을 Full score를 받은 Challenge의 개수를 기준으로 내림차순 정렬하고, 만약 Full score를 받은 Challenge 개수가 동일하다면 hacker_id 를 기준으로 오름차순 정렬해라.

1. 'Submissions' table을 기준으로 필요한 정보를 담고 있는 테이블들을 join한다.
   1) 제출한 문제의 full score를 알기 위해 'Difficulty' table의 score정보가 필요하다.
   2) difficulty_level 정보가 있어야 full score를 연결할 수 있고, 그 정보는 Challenges 테이블에 있다.
   3) hacker의 이름을 출력하기 위해 'Hackers' table이 필요하다.

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

2. '만점'인 컬럼 추출하기
   • 'Difficulty' table의 score는 각 difficulty_level에서의 만점에 대한 정보이기 때문에, 제출한 score가 동일하다면 만점이다.

WHERE D.score=S.score AND D.difficulty_level=C.difficulty_level

3. hacker_id별로 full score 맞은 문제 갯수를 count하고, 두문제 이상 푼 사람만 추출한다.
   • group by로 hacker_id,name을 묶어준 뒤, COUNT(H.hacker_id)>1인 컬럼만 뽑아준다.

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 D.score=S.score AND D.difficulty_level=C.difficulty_level
GROUP BY H.hacker_id, H.name
HAVING COUNT(H.hacker_id)>1

4. 문제 조건에 맞춰 정렬한다.

ORDER BY COUNT(H.hacker_id) DESC, H.hacker_id ASC;

5. 최종코드

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 D.score=S.score AND D.difficulty_level=C.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 ASC;

---

🎈문제해결을 위한 아이디어
1. full score를 맞은 문제가 2개 이상인 참가자들을 알아보기 위해 hacker_id, name을 기준으로 grouping하고, hacker_id를 count한다.