Top Competitors

---

Link

Julia just finished conducting a coding contest, and she needs your help assembling the leaderboard!

1) Write a query to print the respective hacker_id and name of hackers who achieved full scores for more than one challenge.

➡️ FULL SCORE 를 달성한 challenge 가 1개보다 많은 해커의 id와 name 을 출력

2) Order your output in descending order by the total number of challenges in which the hacker earned a full score.

3) If more than one hacker received full scores in same number of challenges, then sort them by ascending hacker_id.

풀이

---

테이블 종류

• Hackers
• Difficulty
• Challenges
• Submissions

FULL SCORE 를 달성한 challenge 가 1개보다 많은 해커의 id와 name 을 출력

1. 각 테이블 조인

SELECT
FROM submissions S
	INNER JOIN challenges C ON S.challenge_id = C.challenge_id
    INNER JOIN hackers H ON S.hacker_id = H.hacker_id
    INNER JOIN difficulty D ON C.difficulty_level = D.difficulty_level

2. difficulty_level 에 맞는 full_score 달성 했다는 조건 달기

SELECT S.hacker_id, H.name, C.challenge_id
FROM submissions S
	INNER JOIN challenges C ON S.challenge_id = C.challenge_id
    INNER JOIN hackers H ON S.hacker_id = H.hacker_id
    INNER JOIN difficulty D ON C.difficulty_level = D.difficulty_level
    
WHERE C.difficulty_level = D.difficulty_level AND S.score = D.score

10857 Kevin 51898 
10857 Kevin 69855 
10857 Kevin 93294 
10857 Kevin 68233 
10857 Kevin 60691 
10857 Kevin 69886 
12539 Paul 60691 
12539 Paul 61757 
12539 Paul 68233 

3. hacker_id, name 으로 GROUP BY 한 후,
   challenge 수가 1개보다 많다는 조건 추가

SELECT S.hacker_id, H.name
FROM submissions S
    INNER JOIN challenges C ON S.challenge_id = C.challenge_id
    INNER JOIN hackers H ON S.hacker_id = H.hacker_id
    INNER JOIN difficulty D ON C.difficulty_level = D.difficulty_level
WHERE C.difficulty_level = D.difficulty_level AND S.score = D.score

GROUP BY S.hacker_id, H.name
HAVING COUNT(C.challenge_id) > 1

5. ORDER BY 정렬조건 추가

ORDER BY COUNT(C.challenge_id) DESC, S.hacker_id

My Answer

---

SELECT S.hacker_id, H.name
FROM submissions S
    INNER JOIN challenges C ON S.challenge_id = C.challenge_id
    INNER JOIN hackers H ON S.hacker_id = H.hacker_id
    INNER JOIN difficulty D ON C.difficulty_level = D.difficulty_level
WHERE C.difficulty_level = D.difficulty_level AND S.score = D.score
GROUP BY S.hacker_id, H.name
HAVING COUNT(C.challenge_id) > 1
ORDER BY COUNT(C.challenge_id) DESC, S.hacker_id

---

• left join 풀이 읽어보기
  https://www.inflearn.com/questions/194525