[SQL 코드카타] 172 ~ 174번 (HackerRank)

172번 Top Competitors

select
    sm.hacker_id, hk.name
from 
    submissions sm
    join hackers hk
    on sm.hacker_id = hk.hacker_id
    join challenges ch
    on ch.challenge_id = sm.challenge_id
    join difficulty df
    on df.difficulty_level = ch.difficulty_level
where
    sm.score = df.score
group by
    sm.hacker_id, hk.name
having
    count(sm.challenge_id) > 1
order by
    count(sm.challenge_id) desc, sm.hacker_id asc

 

173번 Ollivander's Inventory

select
    wd.id, wp.age, wd.coins_needed, wd.power
from 
    wands wd
    join wands_property wp
    on wd.code = wp.code
where
    wp.is_evil = 0
    and wd.coins_needed = (select min(w.coins_needed)
                          from wands w
                          where w.code = wd.code and w.power = wd.power)
order by
    wd.power desc, wp.age desc

 

 

174번 challenges

with cnt_hackers as (
select
    hk.hacker_id, hk.name, count(ch.challenge_id) as cnt_challenge
from
    hackers hk
    join challenges ch
    on hk.hacker_id = ch.hacker_id
group by
    1, 2
) --1) name에 따른 challegne count를 해준 뒤 
select
    hacker_id, name, cnt_challenge
from
    cnt_hackers
where --2) 다음 조건에 만족하는 개수만 조회하기
    cnt_challenge not in (select cnt_challenge
                         from cnt_hackers
                         group by
                            1
                         having
                            count(*) > 1 
                            and cnt_challenge < (select max(cnt_challenge)
                                                from cnt_hackers)
                          )
order by
    cnt_challenge desc, hacker_id

> rank함수를 활용해 max()대신 활용한 답안

WITH cte AS (
    SELECT
        h.hacker_id,
        h.name,
        COUNT(c.challenge_id) AS counter,
        DENSE_RANK() OVER (ORDER BY COUNT(c.challenge_id) DESC) AS number
    FROM
        hackers h
    INNER JOIN
        challenges c ON h.hacker_id = c.hacker_id
    GROUP BY
        h.hacker_id, h.name
)
SELECT
    hacker_id,
    name,
    counter
FROM
    cte
WHERE
    number = 1 OR (SELECT COUNT(DISTINCT number) FROM cte) = 1;