HackerRank SQL - Top Competitors

 

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  space-separated values.

Problem

full score를 2번 이상 맞은 참가자의 hacker_id와 name을 추출하라.

단, 해당 challenges의 수를 기준으로 내림차순, 만약 수가 같다면 hacker_id로 오름차순으로 정렬하라.

---

Answer1

SELECT 
      S.hacker_id, H.name
FROM Challenges C, Difficulty D, hackers H, Submissions S
WHERE S.challenge_id = C.challenge_id
  AND D.difficulty_level = C.difficulty_level
  AND H.hacker_id = S.hacker_id
  AND D.score = S.score
GROUP BY S.hacker_id, H.name HAVING count(C.challenge_id) >= 2
ORDER BY count(*) desc, S.hacker_id asc

 

Answer2

SELECT 
      S.hacker_id, H.name
FROM Submissions S
     INNER JOIN Challenges C ON S.challenge_id = C.challenge_id
     INNER JOIN Difficulty D ON D.difficulty_level = C.difficulty_level
     INNER JOIN hackers H ON H.hacker_id = S.hacker_id
WHERE D.score = S.score
GROUP BY S.hacker_id, H.name HAVING count(C.challenge_id) >= 2
ORDER BY count(*) desc, S.hacker_id asc

좀 더 깔끔하게 inner join으로 작성하면 위와 같다.

속도가 별반 다르진 않은 것 같은데 그래도 가독성은 훨씬 좋다.

---

How to solve

먼저 다중 join이 필요하니, from절에서 필요한 table을 두고 inner join 해준다.

이 때, 기준점수(Difficulty의 score)와 실제 받은 점수(Submission의 score)가 같은 경우를 WHERE절에 조건으로 부여한다.

FROM Challenges C, Difficulty D, hackers H, Submissions S
WHERE S.challenge_id = C.challenge_id
  AND D.difficulty_level = C.difficulty_level
  AND H.hacker_id = S.hacker_id
  AND D.score = S.score

 

그러나 가독성이 좋지 않으니, inner join 구문을 from절에 넣는 것도 좋은 방법이 될 것 같다.

FROM Submissions S
     INNER JOIN Challenges C ON S.challenge_id = C.challenge_id
     INNER JOIN Difficulty D ON D.difficulty_level = C.difficulty_level
     INNER JOIN hackers H ON H.hacker_id = S.hacker_id
WHERE D.score = S.score

 

다음, GROUP BY절에서는 HAVING절로 2개 이상인 challenge_id 수를 조건으로 부여한다. 

GROUP BY S.hacker_id, H.name HAVING count(C.challenge_id) >= 2

여기서 심각한 삽질을 하나 했는데, hacker_id와 h.name 하나의 속성으로 유일한 값이라 생각해서 h.name은 붙이지 않았는데 아래와 같은 오류 메시지를 뱉아냈다.

 

group by 사용시 SELECT의 칼럼 중 집계함수에 쓰이는 것을 제외한 모든 칼럼을 기입하지 않아 발생한 오류

GROUP BY 절에 집계함수를 제외한 나머지 컬럼을 적어주지 않으면 위와 같은 오류가 발생한다.

 

그리고 나서 challenge_id의 수와 hacker_id값의 정렬조건을 완성해준다.

ORDER BY count(*) desc, S.hacker_id asc