해커랭크 - Challenges

https://www.hackerrank.com/challenges/challenges/submissions/database/331359810

 

SELECT h.hacker_id
     , h.name
     , count(*) Challenges_created
FROM Challenges c 
     INNER JOIN Hackers h ON h.hacker_id = c.hacker_id

GROUP BY h.hacker_id, h.name
HAVING Challenges_created = (SELECT MAX(Challenges_created)             --Challeges_created MAX 값이 50인 것         
                                FROM(    
                                    SELECT hacker_id
                                         , count(*) Challenges_created
                                    FROM Challenges
                                    GROUP BY hacker_id
                                    ) sub)
                    
OR Challenges_created IN (SELECT Challenges_created             --만들어진 챌린지수의 묶음이 각각 몇개인지 ex) 몇명의 사람들이 12개의 챌린지를 만들었는지?       
                            FROM(
                                SELECT hacker_id
                                     , COUNT(*) AS Challenges_created               --해커 한명 당 몇개의 챌린지를 만들었는지?
                                FROM Challenges
                                GROUP BY hacker_id
                                )sub
                            GROUP BY Challenges_created
                            HAVING COUNT(*)=1               --12개를 만든 사람이 딱 한명일 때만 목록에 보존
                         )                     


ORDER BY challenges_created DESC, hacker_id



-----------------------------------------------------------------
-----------------------------------------------------------------



Challenges - WITH 문 활용  - Medium
https://www.hackerrank.com/challenges/challenges/submissions/database/331359810
-----------------------------------------------------------------

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 Challenges_created = (SELECT MAX(Challenges_created) FROM counter)
OR Challenges_created IN (SELECT Challenges_created        -- if count(*) is in HAVING, then exclude "count(*)" in SELECT clause
                          FROM counter
                          GROUP BY Challenges_created
                          HAVING count(*)=1 )
ORDER BY counter.Challenges_created DESC, counter.hacker_id