[HackerRank] Challenges

 

 

[문제]

이미지를 누르면 문제 링크로 넘어갑니다

 

 

[MySQL]

WITH challenge_cnts AS (
    SELECT h.hacker_id, h.name, COUNT(c.challenge_id) AS cnt
    FROM Hackers AS h
        LEFT JOIN Challenges AS c ON h.hacker_id = c.hacker_id
    GROUP BY h.hacker_id, h.name)
, same_cnts AS (
    SELECT *, COUNT(*) OVER (PARTITION BY cnt) AS same_cnt
    FROM challenge_cnts)

SELECT hacker_id, name, cnt
FROM same_cnts
WHERE same_cnt = 1
   OR cnt = (SELECT MAX(cnt)
             FROM challenge_cnts)
ORDER BY cnt DESC, hacker_id ASC