HackerRank - Challenges (Using 'subquery', 'with')

문제

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.

Table 1. Hackers

Table 2. Challenges

내 풀이

SELECT HC.HACKER_ID
     , HC.NAME
     , CH.TOTAL
  FROM HACKERS HC
INNER JOIN (   
                SELECT HACKER_ID
                     , COUNT(CHALLENGE_ID) AS TOTAL
                FROM CHALLENGES 
                GROUP BY HACKER_ID
                HAVING COUNT(CHALLENGE_ID) IN (
                    SELECT BT.TOT 
                    FROM (
                        SELECT A.TOTAL AS TOT
                             , COUNT(A.TOTAL) AS CNT
                        FROM (
                                SELECT HACKER_ID
                                     , COUNT(CHALLENGE_ID) AS TOTAL
                                FROM CHALLENGES 
                                GROUP BY HACKER_ID
                            ) A 
                       GROUP BY A.TOTAL
                       HAVING COUNT(A.TOTAL) = 1 
                           OR A.TOTAL = (
                                    SELECT MAX(TEMP.TOTAL) AS CNT
                                    FROM (
                                            SELECT HACKER_ID
                                                 , COUNT(CHALLENGE_ID) AS TOTAL    
                                            FROM CHALLENGES 
                                            GROUP BY HACKER_ID
                                        ) TEMP 
                           )
                    ) BT         
                )       
) CH
ON HC.HACKER_ID = CH.HACKER_ID
           
ORDER BY TOTAL DESC
       , HACKER_ID ASC

강사님 풀이 1

SELECT HC.HACKER_ID
     , HC.NAME
     , COUNT(*) AS CHALLENGES_CREATED
FROM CHALLENGES CH
    INNER JOIN HACKERS HC ON CH.HACKER_ID = HC.HACKER_ID
GROUP BY HC.HACKER_ID, HC.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

강사님 풀이 2: WITH 사용

-- 재사용 가능한 테이블 만들기
WITH COUNTER AS (
    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
)

SELECT *
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 CHALLENGES_CREATED DESC, HACKER_ID