테이블 구조

문제1

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.

💻 만점 문제를 두 개 이상 받은 사람 구하기
(맞춘문제 내림차순, 아이디 오름차순)

SELECT b01.hacker_id
      ,b01.name
FROM
(
SELECT a01.hacker_id
     ,a04.name
     ,COUNT(distinct a01.challenge_id) chal_count
FROM submissions a01
   INNER JOIN challenges a02 ON a01.challenge_id = a02.challenge_id
   INNER JOIN difficulty a03 ON a02.difficulty_level = a03.difficulty_level
                             AND a01.score = a03.score
   INNER JOIN hackers a04    ON a01.hacker_id = a04.hacker_id
GROUP BY a01.hacker_id
       ,a04.name
)b01
WHERE b01.chal_count>1
ORDER BY b01.chal_count DESC
       ,b01.hacker_id ASC
   ;

✍ INNER JOIN 을 통해 만점 받은 사람 소싱 후, 맞춘 문제 카운팅
✍ 서브쿼리를 이용해서, 한개 이상 맞춘 사람 추출
✍ 서브쿼리를 안쓰고, having 절을 써도 됨.