[HackerRank lv6] SQL문제풀이 - Challenges

👉 문제 바로가기(HackerRank)

# 문제

Q. hackers 테이블과 challenges 테이블에서 해커별로 출제한 challenge 수를 출력하라. challenge 수가 겹칠 때 challenge 수가 최고라면 전부 출력하라. challenge 수가 겹치는데 최고가 아니라면 전부 제거하라

ex)

설명을 간단하게 위해 생략했지만
실제로는 hacker_id, hacker_name, challenge 수를 출력해야한다.

조건별로 출력을 달리해야하는 괴랄한 문제이다.
window함수 사용이 필수적이다. 모른다면 아예 풀 수가 없다.
지난 가비아코딩테스트 문제에서 거의 동일한 유형을 풀어보아서 어떻게든 풀어낸 것 같다.

# 정답

# mysql
select 
    h.hacker_id
    , h.name
    , handc.total_number
from 
    hackers h
    , (
        select 
            h.hacker_id
            , count(*) total_number
        from
            hackers h
            , challenges c
        where 
            h.hacker_id = c.hacker_id
        group by 
            h.hacker_id
    ) handc
    , (
        select 
            count(*) max_number
        from 
            hackers h
            , challenges c
        where 
            h.hacker_id = c.hacker_id
        group by 
            h.hacker_id
        order by 1 desc
        limit 1
    ) max_count
where
    h.hacker_id = handc.hacker_id
    and handc.total_number = max_count.max_number
union all ###############################################
select 
    hcrn.hacker_id
    , h.name
    , hcrn.total_number
from
    hackers h
    , (    
        select
            count(*) over(partition by rnk) cnt
            , hacker_id
            , total_number
        from
            (
                select
                    dense_rank() over(order by count(*) desc) rnk
                    , h.hacker_id
                    , count(*) total_number
                from
                    hackers h
                    , challenges c
                where 
                    h.hacker_id = c.hacker_id
                group by 
                    h.hacker_id
            ) hc
        where rnk > 1
    ) hcrn
where 
    h.hacker_id = hcrn.hacker_id
    and cnt = 1
order by 
    3 desc
    , 1
;

1. challenge 수가 최대인 부분(수가 겹쳐도 출력)
   • name을 얻기 위한 hackers
   • hackers와 challenges를 조인하여 group by 한 테이블
   • 최대 수를 가진 one컬럼 one데이터 테이블
2. challenge 수가 최대가 아닌 부분(수가 겹치는 것은 제거)
   • name을 얻기 위한 hackers
   • challenge 수를 랭크하고 중복되는 갯수를 계산한 테이블

# 최적화

select 
    hacker_id
    , name
    , total_number
from 
    (
        select 
            h.hacker_id
            , h.name
            , count(*) total_number
            , rank() over(order by count(h.hacker_id) desc) rnk
        from
            hackers h
            , challenges c
        where 
            h.hacker_id = c.hacker_id
        group by 
            h.hacker_id, h.name
    ) handc
where 
    rnk = 1
union all
select 
    hacker_id
    , name
    , total_number
from
    (    
        select
            h.hacker_id
            , h.name
            , count(*) total_number
            , dense_rank() over(order by count(*) desc) rnk
            , count(count(*)) over(partition by count(*)) cnt
        from
            hackers h
            , challenges c
        where 
            h.hacker_id = c.hacker_id
        group by 
            h.hacker_id, h.name
    ) hc
where 
    cnt = 1
    and rnk > 1
order by 
    3 desc
    , 1
;

윈도우 함수의 동작방식을 이해하면 위와 같은 쿼리를 작성할 수 있다.
윈도우 함수도 기본적으로 group by의 컬럼구분을 따라가기 때문에 다시 그 안에서 count(count(*))를 하는 방식으로 중복수를 구한다.

# 괴물의 답변

discussion탭에서 다른 사람들의 답변을 보던 중 엄청나게 최적화된 쿼리를 발견했다.
비슷한 유형의 문제들을 풀 때 엄청나게 도움이 될 듯 한데
구문을 줄이는게 미친듯이 효율적이라 오히려 처음보는 사람은 '이게 뭐지' 싶을수 있겠다...

서브쿼리를 거의 사용하지 않고 window함수 안에 집계함수를 넣어서☠️ '중복 된 카운트'를 세고
WHERE 절에 중복조건에 NOT을 걸어서☠️ 최고 수 일때와 아닐때를 전부 아우르는 조건을 설정했다.

정말 똑똑한 사람은 많은 것 같다... 분발하자😭

SELECT 
    hacker_id
    , name
    , totalcount
FROM
    (
        SELECT 
            Hackers.hacker_id,
            name, 
            COUNT(challenge_id) AS totalcount,
            COUNT(COUNT(challenge_id)) OVER (PARTITION BY COUNT(challenge_id)) as dupes,
            MAX(COUNT(challenge_id)) OVER () as max
        FROM 
            Hackers 
            LEFT JOIN Challenges USING(hacker_id)
        GROUP BY 
            Hackers.hacker_id, name
    ) B
WHERE 
    NOT (dupes >1 && totalcount < max)
ORDER BY 
    totalcount desc
    , hacker_id