210715 텍스트북, SQL

텍스트북

4주차 비즈니스 모델 케이스

SQL

인프런 백문이불여일타 SQL 고급반

• ERD
• 서브쿼리

Challenges | HackerRank

WITH ta AS (
    SELECT  Challenges.hacker_id
        ,   Hackers.name
        ,   COUNT(challenge_id) AS challenges_created
    FROM    Challenges
    INNER JOIN Hackers ON Challenges.hacker_id = Hackers.hacker_id
    GROUP BY Challenges.hacker_id, Hackers.name
)
, tb AS (
    SELECT  challenges_created
        ,   COUNT(hacker_id) AS id_cnt
    FROM    ta
    GROUP BY challenges_created
)
, tc AS (
    SELECT  hacker_id
        ,   RANK() OVER (ORDER BY challenges_created DESC) AS RK
    FROM    ta
)

SELECT  hacker_id
    ,   name
    ,   challenges_created
FROM    ta
WHERE   challenges_created IN (
                                    SELECT  challenges_created
                                    FROM    tb
                                    WHERE   id_cnt =1
                                )
    OR hacker_id IN (
                        SELECT  hacker_id
                        FROM    tc
                        WHERE   RK =1
                )
ORDER BY challenges_created DESC, hacker_id