SQL (다중 JOIN) - Top Competitors

문제

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

목표/풀이

[리더보드 만들기]

1. 조회: 해커 아이디, 이름을 출력하기
2. 조건: 단 2개 이상의 챌린지에서 만점을 받은 사람에 한해서
3. 정렬 : 만점 받은 챌린지 개수 내림차순, 아이디 오름차순

---

[테이블 살펴보기]

• hackers
  • hacker_id
  • name
• difficulty
  • difficulty_level
  • score : 최대점수(=만점) 💯
• challenges
  • challenge_id
  • hacker_id : 챌린지를 만든 아이디 ⭐
  • difficulty_level
• submissions
  • submission_id : 제출 아이디
  • hacker_id : 제출한 아이디(=문제를 푼 아이디) ⭐
  • challenge_id
  • score: 자기점수💯

---

[문장을 SQL 코드로 맵핑]

• print the respective hacker_id and name  (해커 아이디와 이름을 출력하세요)
  • ⇒ SELECT hacker_id, name
• who achieved full scores for more than one challenge (2개 이상의 챌린지에서 만점을 받은 사람만)
  • ⇒ COUNT(challenge_id) > 1
  • ⇒ difficulty_level이 같을 때, WHRE s.score = d.score
• order by the total number of challenges in which the hacker earned a full score. / then sort them by ascending hacker_id. (만점 받은 챌린지 개수 내림차순, 해커 아이디 오름차순)
  • ⇒ COUNT() DESC, hacker_id ASC

틀린 코드

• submissions ↔ challenges 관계 해석 오인
  • s.hacker_id는 문제를 푼 사람, c.hacker_id는 문제를 만든 사람으로 같지 않는데, 컬럼명이 똑같아서 연결했다. 테이블의 컬럼 간 관계를 제대로 파악하자 (무엇을 기준으로 연결할 수 있을지)
• 챌린지 개수 세기
  • COUNT(hacker_id)로 함.
  • 해커 아이디를 세는 것이 아니라 만점 받은 챌린지 개수를 세는 것이므로 COUNT(challenge_id)가 되어야 함

SELECT s.hacker_id , h.name , COUNT(s.hacker_id) 
FROM submissions s 
	INNER JOIN challenges c ON s.hacker_id = c.hacker_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 s.hacker_id, h.name
ORDER BY COUNT(s.hacker_id) DESC, s.hacker_id

정답 코드

/*
[로직]
1. 만점 챌린지가 2개 이상인 해커 정보를 구하기 위해서 난이도, 만점 점수, 자기 점수 등을 결합해서 볼 필요가 있음 -> 조인
2. submissions ---- challenge_id ----- challenges
3. challenges ---- difficulty_level ---- difficulty
4. difficulty --- hacker_id ---- hackers
5. 본인 점수가 만점인 경우 : s.score = d.score
6. 해커 아이디, 이름 별 그룹핑
7. 이때 챌린지 2개 이상 만점자에 한함 HAVING COUNT(...) > 1 
8. 정렬조건 추가 
*/

SELECT s.hacker_id , h.name , COUNT(s.hacker_id) 
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 s.hacker_id, h.name
HAVING COUNT(s.challenge_id) > 1
ORDER BY COUNT(s.challenge_id)  DESC, s.hacker_id ASC;

주의사항

1. 최종적으로 구하려는 값을 가지고 어떻게 조인하면 좋을지 생각하기
   • 이때 연결하려는 두 테이블의 관계를 잘 살펴보기
   • 어떤 컬럼을 기준으로 연결해야 하는가
   • 1:1 관계인가 1:N 관계인가 
   • 특별한 결합 조건이 없는 INNER JOIN인가, 한쪽 테이블의 모든 정보가 포함되어야 하는 LEFT JOIN 인가 
2. 챌린지 개수를 세고 있음
   • COUNT(s.challenge_id) 떠올리기