MySQL | HackerRank | Top Competitors

Question

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.

---

Input Format

The following tables contain contest data:

• Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.
  
• Difficulty: The difficult_level is the level of difficulty of the challenge, and score is the score of the challenge for the difficulty level.
  
• Challenges: The challenge_id is the id of the challenge, the hacker_id is the id of the hacker who created the challenge, and difficulty_level is the level of difficulty of the challenge.
  
• Submissions: The submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, challenge_id is the id of the challenge that the submission belongs to, and score is the score of the submission.
  

---

Sample Input

Hackers Table:

Difficulty Table:

Challenges Table:

Submissions Table:

Sample Output

90411 Joe

Explanation

Hacker 86870 got a score of 30 for challenge 71055 with a difficulty level of 2, so 86870 earned a full score for this challenge.

Hacker 90411 got a score of 30 for challenge 71055 with a difficulty level of 2, so 90411 earned a full score for this challenge.

Hacker 90411 got a score of 100 for challenge 66730 with a difficulty level of 6, so 90411 earned a full score for this challenge.

Only hacker 90411 managed to earn a full score for more than one challenge, so we print the their hacker_id and name as 2 space-separated values.

 

Summary

JOIN을 자유자재로 할 수 있는가?

1. Submissions 테이블에 근거하여, hacker의 점수가 difficulty_level에 따라 만점을 받은 hacker를 가려내라
2. 이 때, 만점을 받은 Challenge가 한 개를 넘는, 즉 2개 이상의 Challenge에서 만점을 받은 hacker를 가려내라
3. 만점을 받은 총 Challenge 수를 내림차순으로 정렬하고, 해당 수가 같은 경우 hacker_id를 오름차순으로 정렬해라

---

Solve

1. Hackers 테이블과 조인하기 전, Submission 태이블을 기준으로 필요한 컬럼들을 함께 볼 수 있는지 확인했다.
   • Challenges테이블과 연결하여 difficulty_level 컬럼을 가져오고, Difficulty테이블과 연결하여 만점 score 컬럼까지 가져왔다.
   • 이 때, Submissions에 해커들이 받은 점수와 Difficulty의 만점 점수가 같은지 확인하여 Where절에 생성할 조건을 미리 주었다.
2. 해커들의 점수와 만점의 점수가 동일하게 매칭되어 출력되는지 확인을 마친 후, 두 개이상의 챌린지에서 만점을 받은 해커들을 가려낼 수 있도록 조건을 추가했다.
   
   • 집계함수를 사용할 경우 해당 쿼리 내 Where 절에서 해당 집계함수를 조건으로 사용할 수 없다.
   • 이럴때는 Having 절을 이용하여 집계함수를 해당 쿼리 내에 조건으로 사용하여 결과값을  필터링할 수 있다.
   • Group by를 할 때 주의할 점은 단일 컬럼 여러개와 집계 함수를 한개 사용했을 경우, 사용한 단일컬럼을 모두 Group by 절에 기재해주어야 한다.
3. 문제가 제시한 조건에 맞게 정렬을 한다.

SELECT concat(h.hacker_id, '  ', h.name)
FROM Submissions s
    JOIN Challenges c ON s.challenge_id = c.challenge_id
    JOIN Difficulty d ON c.difficulty_level = d.difficulty_level AND s.score = d.score
    JOIN Hackers h ON s.hacker_id = h.hacker_id                     
GROUP BY h.hacker_id, h.name
HAVING count(s.challenge_id) > 1
ORDER BY count(s.challenge_id) desc, h.hacker_id

 

Issue

• row_number()over(partition by hacker_id, challenge_id)로 접근하여 풀이해보려고 했는데 row_number함수만 사용하면 에러가 났다. > 왠지 모르겠음..🧐
• with 문 사용하면 에러가 났다. > discussions에서 with문을 사용한 쿼리가 있었는데 왜 안되는지 모르겠다 🧐🧐 (MySQL 버전 이슈)

SELECT concat(h.hacker_id, '  ', h.name)
FROM Hackers h 
    JOIN (  SELECT s.hacker_id, count(s.challenge_id) cnt
            FROM Submissions s
                JOIN Challenges c ON s.challenge_id = c.challenge_id
                JOIN Difficulty d ON c.difficulty_level = d.difficulty_level
                                     AND s.score = d.score
            GROUP BY s.hacker_id
            HAVING cnt > 1)a ON h.hacker_id = a.hacker_id
ORDER BY a.cnt DESC, h.hacker_id

• 그래서 with 문의 내용을 이중쿼리 형식으로 사용해서 조인했다.

with a as (
SELECT s.hacker_id, count(s.challenge_id) cnt
FROM Submissions s
    JOIN Challenges c ON s.challenge_id = c.challenge_id
    JOIN Difficulty d ON c.difficulty_level = d.difficulty_level
                         AND s.score = d.score
GROUP BY s.hacker_id
HAVING cnt > 1)

SELECT concat(h.hacker_id, '  ', h.name)
FROM Hackers h 
    JOIN a ON h.hacker_id = a.hacker_id
ORDER BY a.cnt DESC, h.hacker_id

• 생각해보니 Having 절로 count 집계함수 조건적용이 가능한데, 굳이 서브쿼리를 넣을 필요가 없었고 최종 문제풀이 쿼리로 작성 완료하였다. 👌

 

이번 쿼리는 평소에 이중쿼리나, with문을 많이 썼던 필자에게 쿼리 성능면에서의 반성과 고찰을 많이하게 한 쿼리였다.

SQL은 어찌보면 단순하지만 사람마다 짜는 법도, 사용하는 법도 달라서 재밌기도하고 어려운 것도 같다. 👀

---

Source

https://www.hackerrank.com/challenges/full-score/problem?isFullScreen=true 

Top Competitors | HackerRank