Challenges / HackerRank, SQL, MySQL

문제

source: HackerRank

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.

[Input Format]

The following tables contain challenge data:
- Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.
- Challenges: The challenge_id is the id of the challenge, and hacker_id is the id of the student who created the challenge.

 

 

 

 

 

제출답안(MySQL)

WITH 
    chl_by_h AS 
        (SELECT hacker_id, COUNT(challenge_id) AS cch
         FROM challenges
         GROUP BY hacker_id), 
     
    chlcrtd_dupe AS 
        (SELECT cch
         FROM chl_by_h
         WHERE cch != (SELECT MAX(cch) FROM chl_by_h)
         GROUP BY cch
         HAVING COUNT(cch) > 1)


SELECT h.hacker_id, h.name, cch
FROM hackers AS h 
    LEFT JOIN chl_by_h AS cb 
    ON h.hacker_id = cb.hacker_id
WHERE cch IS NOT NULL AND cch NOT IN (SELECT * FROM chlcrtd_dupe)
ORDER BY 3 DESC, 1

 

 

 

풀이(MySQL)

해커 ID와 해커별로 생성한 challenge 수를 가진 서브쿼리 생성

>> 편의를 위해 해커별로 생성한 challenge 수를 CCH (Count_Challenges_by_Hacker) 로 표기하겠음

WITH 
    chl_by_h AS 
        (SELECT hacker_id, COUNT(challenge_id) AS cch
         FROM challenges
         GROUP BY hacker_id),

>> chl_crtd : 해커별로 생성한 challenge_id의 카운트 값을 담은 칼럼

>> chl_by_h 는 해커별로 생성한 challenge_id 갯수를 가짐

 

[chl_by_h] 에서 2개 이상 나온 CCH를 가진 서브쿼리 생성

>> 조건을 충족하는 데이터를 제거하기 위한 목록

>> 다만, CCH가 max(CCH) 의 경우는 포함되어야하므로 해당 서브쿼리에 포함시키지 않음

    chlcrtd_dupe AS 
        (SELECT cch
         FROM chl_by_h
         WHERE cch != (SELECT MAX(cch) FROM chl_by_h)
         GROUP BY cch
         HAVING COUNT(cch) > 1)

 

조인 후 조건에 필요한 데이터 출력

>> hacker_id, name, cch 를 SELECT

>> cch 가 null 이 아니거나 중복이 된 cch를 가지지 않은 값을 한정하여 출력

>> cch 기준 내림차순, hacker_id 기준 오름차순

SELECT h.hacker_id, h.name, cch
FROM hackers AS h 
    LEFT JOIN chl_by_h AS cb 
    ON h.hacker_id = cb.hacker_id
WHERE cch IS NOT NULL AND cch NOT IN (SELECT * FROM chlcrtd_dupe)
ORDER BY 3 DESC, 1

 

 

 

 

문제 바로가기(MySQL)