Top Competitors

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.

챌린지에서 만점을 얻은 해커의 이름과 ID 출력하는 문제다. 단, 2개 이상 만점을 얻은 해커만 해당한다. 그후 만점을 획득한 총 챌린지 수를 기준으로 내림차순 정렬, 그 다음 두 명 이상의 해커가 같은 수의 챌린지에서 만점을 받은 경우 id를 기준으로 오름차순 정렬한다.

SELECT h.hacker_id, h.name
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 h.hacker_id = s.hacker_id
WHERE s.score = d.score
GROUP BY 1, 2 -- GROUP BY를 했을 때 하나 이상 만점이어야 하니까 조건을 HAVING에 써준다
HAVING COUNT(DISTINCT s.submission_id) > 1
ORDER BY COUNT(DISTINCT s.submission_id) DESC, h.hacker_id

💡

• 공통되는 게 없으면 소용 없으니까 INNER JOIN을 사용한다.
• 만점이 아니면 소용이 없으니까!
• 1) 기준은 제출한 점수가 적힌 submission 테이블
• 2) full score 받은 사람을 찾으려면 difficulty 테이블의 점수가 필요하고, submission 테이블과 연결하려면 challenges 테이블의 difficulty_level이 필요하다.
  3) submission 테이블과 challenges 테이블을 INNER JOIN 하고 그 다음 difficulty 테이블과 INNER JOIN 한다!

💡💡
TIP. 언제 INNER JOIN과 LEFT JOIN을 사용하나?

• 공통되는 것만 가져오고 싶을 때!와 겹치는 게 없어도 가져오고 싶은 게 있을때!

-- 통과된 오답
-- 1. hacker의 이름(name)을 출력하기 위해선 Hacker 테이블이 필요하다
-- 2. full score를 얻기 위해서는 Difficulty에 있는 score가 필요하다
-- 3. difficulty level 정보는 Difficulty 테이블과 Challenges 테이블에 있다.
-- 4. 만점 받은 총 챌린지의 정보를 알기 위해서는 Submissions 테이블의 정보가 필요하다
-- => 모든 테이블을 JOIN 해야 한다!!!
-- 5. GROUP BY 한 칼럼만 SELECT, HAVING절에서 COUNT할 수 있다

-- 
SELECT s.hacker_id, h.name
FROM Hackers h 
LEFT JOIN Submissions s ON h.hacker_id = s.hacker_id
LEFT JOIN Challenges c ON s.challenge_id = c.challenge_id
LEFT JOIN Difficulty d ON c.difficulty_level = d.difficulty_level
WHERE s.score = d.score
GROUP BY s.hacker_id, h.name
HAVING count(s.hacker_id) >= 2 -- GROUP을 제한하는 역할! 즉, GROUP BY의 조건절
ORDER BY count(s.hacker_id) DESC, s.hacker_id

-- 나의 오답... 정렬을 위해 WHERE 부속질의의 별칭을 만들었는데 먹히지 않았다
SELECT h.hacker_id, h.name
FROM Hackers h 
LEFT JOIN Submissions s ON h.hacker_id = s.hacker_id
LEFT JOIN Challenges c ON s.challenge_id = c.challenge_id
LEFT JOIN Difficulty d ON c.difficulty_level = d.difficulty_level
WHERE (SELECT count(s.hacker_id)
FROM Submissions
WHERE s.score = d.score) >= 2 AS cs
ORDER BY cs DESC, h.hacker_id

💡

• 챌린지에서 만점을 얻은 해커의 이름과 ID 출력 => WHERE 조건은 만점!
• 단, 2개 이상 만점을 얻은 해커만 해당 => HAVING 절에 해당!