HackerRank : Challenges

https://www.hackerrank.com/challenges/challenges/problem?h_r=internal-search&isFullScreen=true 

Challenges | HackerRank

 

Sub Query

SELECT H.HACKER_ID, MAX(H.NAME), COUNT(C.CHALLENGE_ID) AS NUM_OF_CHALLENGES
FROM HACKERS H
JOIN CHALLENGES C ON H.HACKER_ID = C.HACKER_ID
GROUP BY H.HACKER_ID
HAVING NUM_OF_CHALLENGES = (SELECT MAX(SUB.NUM_OF_CHALLENGES)
                            FROM (SELECT COUNT(CHALLENGE_ID) AS NUM_OF_CHALLENGES  
                                  FROM CHALLENGES
                                  GROUP BY HACKER_ID) SUB)
OR NUM_OF_CHALLENGES IN (SELECT SUB.NUM_OF_CHALLENGES
                         FROM (SELECT HACKER_ID, COUNT(*) AS NUM_OF_CHALLENGES
                               FROM CHALLENGES
                               GROUP BY HACKER_ID) SUB
                         GROUP BY SUB.NUM_OF_CHALLENGES
                         HAVING COUNT(*) = 1)
ORDER BY NUM_OF_CHALLENGES DESC, H.HACKER_ID

 

여러번 쓰이는 SUB QUERY는 WITH 문으로 미리 테이블을 정의하여 재사용하면

코드를 간략화 할 수 있다.

WITH CHALLENGES_COUNT AS(
SELECT COUNT(CHALLENGE_ID) AS NUM_OF_CHALLENGES  
FROM CHALLENGES
GROUP BY HACKER_ID)

SELECT H.HACKER_ID, MAX(H.NAME), COUNT(C.CHALLENGE_ID) AS NUM_OF_CHALLENGES
FROM HACKERS H
JOIN CHALLENGES C ON H.HACKER_ID = C.HACKER_ID
GROUP BY H.HACKER_ID
HAVING NUM_OF_CHALLENGES = (SELECT MAX(SUB.NUM_OF_CHALLENGES)
                            FROM CHALLENGES_COUNT SUB)
OR NUM_OF_CHALLENGES IN (SELECT SUB.NUM_OF_CHALLENGES
                         FROM CHALLENGES_COUNT SUB
                         GROUP BY SUB.NUM_OF_CHALLENGES
                         HAVING COUNT(*) = 1)
ORDER BY NUM_OF_CHALLENGES DESC, H.HACKER_ID