해커랭크(HackerRank) MySQL-Challenges

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, Hackers.hacker_id

 

with문을 사용하면 코드가 간결해질 수 있을텐데, 해커랭크의 MySQL에서는 with문 실행이 안된다. 

그래서 MS SQL로 해보았다.

WITH counts AS (SELECT hacker_id,
                        count(*) as challenges_created
                FROM Challenges
                GROUP BY hacker_id
                )
                
SELECT counts.hacker_id,
        name,
        challenges_created
FROM counts
INNER JOIN Hackers on Hackers.hacker_id=counts.hacker_id
WHERE challenges_created = (SELECT MAX(challenges_created) FROM counts)
OR challenges_created IN (SELECT challenges_created FROM counts GROUP BY challenges_created HAVING COUNT(*)=1)
ORDER BY challenges_created DESC

 

challenges_created가 최댓값이 아니면서 카운트가 중복되는 경우 제외하기 부분이 좀 어렵다.