[mySQL] 해커랭크 Challenges

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

1. HAVING 절에
   • challenges_created = max(challenges_created),
   • challenges_created =/ max(challenges_created)
     각각의 조건을 걸어서 작성하는 쿼리

• 쿼리가 길고 복잡하다는 단점 존재

SELECT h.hacker_id,
       h.name,
       COUNT(*) AS challenges_created
FROM Hackers AS h
     INNER JOIN Challenges AS c ON h.hacker_id = c.hacker_id
GROUP BY h.hacker_id, h.name
HAVING challenges_created = (SELECT MAX(challenges_created)
                             FROM (
                                   SELECT hacker_id, COUNT(*) AS challenges_created
                                   FROM Challenges
                                   GROUP BY hacker_id) AS sub)
OR challenges_created IN 
        (SELECT challenges_created
         FROM (
               SELECT hacker_id, COUNT(*) AS challenges_created
               FROM Challenges
               GROUP BY hacker_id) AS sub
         GROUP BY challenges_created
         HAVING COUNT(*) = 1)                                  
ORDER BY challenges_created DESC, h.hacker_id ASC

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 counter.hacker_id, 
       counter.name, 
       counter.challenges_created
FROM counter
WHERE counter.challenges_created = (SELECT MAX(challenges_created) FROM counter)
OR counter.challenges_created IN 
                    (SELECT challenges_created 
                     FROM counter 
                     GROUP BY challenges_created 
                     HAVING COUNT(challenges_created) = 1)
ORDER BY counter.challenges_created DESC, counter.hacker_id ASC