인프런- 데이터 분석을 위한 고급 SQL: 섹션3 - 서브쿼리 Challenges 문제풀이(틀림)

인프런 강의 <데이터 분석을 위한 고급 SQL>을 듣고, 중요한 점을 정리한 글입니다.

문제(틀림)

Julia asked her students to create some coding challenges. Write a query to print the hacker_id, name, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.

풀이

• 나의 틀린 풀이과정

SELECT C.hacker_id, H.name, COUNT(*) AS C
FROM Hackers AS H
     INNER JOIN Challenges AS C ON H.hacker_id = C.hacker_id
GROUP BY H.name, C.hacker_id
ORDER BY C DESC
       , H.hacker_id

: 에러없이 이름, hacker_id, 만든 챌린지 수를 만든 챌린지 수의 내림차 순으로 출력했다. 근데 여기서 만든 챌린지 수가 동일한 경우, 최대 수보다 작은 수는 없애라는 조건을 구현하는 데서 막혔다.

• 선생님 풀이

1. WITH문 활용 안 한 풀이

SELECT Hackers.hacker_id
     , Hackers.name
     , COUNT(*) AS challenges_created
FROM Challenges
     INNER JOIN Hackers ON Challenges.hacker_id = Hackers.hacker_id
GROUP BY Hackers.hacker_id, Hackers.name
HAVING challenges_created = (SELECT MAX(challenges_created)
                            FROM (
                                SELECT hacker_id
                                , COUNT(*) AS challenges_created
                                FROM Challenges
                                GROUP BY hacker_id
                            ) sub)
OR challenges_created IN (SELECT challenges_created
                          FROM (
                                SELECT hacker_id
                                     , COUNT(*) AS challenges_created
                                FROM Challenges
                                GROUP BY hacker_id
                                ) sub
                          GROUP BY challenges_created
                          HAVING COUNT(*) = 1)
ORDER BY Challenges_created DESC, hacker_id

WITH문 활용한 풀이

WITH counter AS (
    SELECT Hackers.hacker_id
         , Hackers.name
         , COUNT(*) challenges_created
    FROM Challenges
         INNER JOIN Hackers ON Challenges.hacker_id = Hackers.hacker_id
    GROUP BY Hackers.hacker_id, Hackers.name
)

SELECT counter.hacker_id
     , counter.name
     , counter.challenges_created
FROM counter
WHERE challenges_created = (SELECT MAX(challenges_created) FROM counter)
OR challenges_created IN (SELECT challenges_created
                          FROM counter
                          GROUP BY challenges_created
                          HAVING COUNT(*) = 1)
ORDER BY counter.challenges_created DESC, counter.hacker_id

배운점

• 답을 낼 때, SELECT문에 집계함수가 있으면 GROUP BY를 써주고, IF조건이 있으면 MAIN문의 GROUP BY 뒤에 HAVING절 서브쿼리를 활용함으로써 문제를 해결할 수 있다. IF조건이 여러개 열거되면, HAVING절에 OR로 조건문 내용을 포함한 서브쿼리를 열거할 수 있다.

• WITH문으로 서브쿼리를 더 간단히 구현할 수 있다. WITH counter(변수명) AS ()문에 반복되는 서브쿼리를 넣는다. 그 다음 전체 쿼리문을 구현하면서, 반복되며 사용되는 서브쿼리는 counter로 대신해 쓰며 쿼리를 구현한다.