[해커랭크] MySQL 서브쿼리 - Top Competitors

 

---

 

Top Competitors | HackerRank

문제)
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

Hackers 테이블, 출처:해커랭크

Difficulty 테이블, 출처 : 해커랭크

Challenges 테이블, 출처 : 해커랭크

Submissions 테이블, 출처:해커랭크

해커 id와 이름을 출력하는데 챌린지에서 만점을 받은 횟수가 1개를 초과한 사람들만 출력하는 문제입니다.

 

정렬은 만점 받은 챌린지의 수를 기준으로 내림차순 정렬을 하고, 점수가 동일할 경우에는 해커 id 기준으로 오름차순 정렬을 합니다.

 

 

JOIN을 활용해 테이블을 묶은 뒤 만점을 받은 Submission_id만 먼저 골라내야할 것 같습니다.

select s.hacker_id
      , h.name
      , count(*) as cnt
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 #만점 받는 조건
group by 1, 2
having count(*) >= 2

위와 같이 테이블을 JOIN으로 묶어준 뒤에 Difficulty 테이블에 있는 점수는 만점 점수이고 Submissions 테이블에 있는 점수가 각 해커가 받은 점수이므로 두 테이블의 점수가 같으면 만점으로 분류할 수 있습니다. (difficulty.score = submissions.score)

 

GROUP BY 함수를 활용해 각 해커 별로 만점을 몇 번 받았는지 COUNT를 해줍니다. 그리고 HAVING 절을 통해 만점을 1개 초과한 해커들만 출력해줍니다.

select hacker_id
     , name
from
    (
    select s.hacker_id
         , h.name
         , count(*) as cnt
    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
    group by 1, 2
    having count(*) >= 2
    ) as full_score
order by cnt desc, hacker_id

마지막으로 정렬 조건을 설정하면 쿼리가 완성됩니다.

 

---