[HackerRank lv6] SQL문제풀이 - 15 Days of Learning SQL (Oracle)

👉 문제 바로가기(HackerRank)
Question.
SQL 학습 컨테스트는 2016년 3월 1일부터 15일까지 15일간 진행된다.
1. 날짜별로 하루도 빠지지 않고 과제를 제출한 unique한 hacker_id의 수
2. 각 날짜별 최대 제출수를 기록한 해커의 hacker_id와 name
을 날짜별로 출력하라

-- day | count | id | name
2016-03-01 4 20703 Angela
2016-03-02 2 79722 Michael
2016-03-03 2 20703 Angela
2016-03-04 2 20703 Angela
2016-03-05 1 36396 Frank
2016-03-06 1 20703 Angela
~

sql문제는 난이도가 높아질수록 프로그래밍 언어에서 해야 될 일을 하고있다는 느낌을 받는데, 잘못된 테이블 설계를 SQL로 대체하는 억지스러운 느낌이 들 때가 있다.
이 문제는 그나마 좀 와닿는 편이라 집중이 쉬웠다.

문제의 요건이 되는 핵심 부분은 위에서 설명한 것처럼 두가지이다.
1. 날짜별 unique한 hacker_id의 수(연속 참여한)
2. 날짜별 최대 제출자

이 두가지를 window sort를 써서 각각 구하고, (한 쿼리에서 같이 구할 수 없다.)
조인하여 문제에서 요구하는 순서대로 출력을 하면 정답이다.

# 정답

select
    t1.submission_date
    , t1.cnt
    , t2.hacker_id
    , t2.name
from
    (
    select
        s.submission_date
        , h.hacker_id
        , h.name
        , count(*) cnt
        , rank() over(partition by s.submission_date order by count(*) desc, h.hacker_id) rnk
    from
        submissions s
        , hackers h
    where
        h.hacker_id = s.hacker_id
    group by
        s.submission_date
        , h.hacker_id
        , h.name
    ) t2
    ,(
    select
        submission_date
        , count(*) cnt
    from
        (
        select 
            submission_date
            , hacker_id
            , sum(1) over(partition by hacker_id order by submission_date) submitted_days
        from 
            submissions s
        group by 
            submission_date
            , hacker_id        
        ) t
    where 
        submitted_days = submission_date - to_date('2016-03-01','yyyy-mm-dd') + 1
    group by
        submission_date
    ) t1
where 
    t1.submission_date = t2.submission_date
    and rnk = 1
order by
    t1.submission_date
;

from절에 존재하는 서브쿼리를 각각 설명하고 글을 마무리하겠다.

# 풀이

table1 - 연속 참여한 unique한 hacker_id의 수

select
    submission_date
    , count(*)
from
    (
    select 
        submission_date
        , hacker_id
        , sum(1) over(partition by hacker_id order by submission_date) submitted_days
    from 
        submissions s
    group by 
        submission_date
        , hacker_id        
    ) t
where 
    submitted_days = submission_date - to_date('2016-03-01','yyyy-mm-dd') + 1
group by
    submission_date

이 쿼리의 쟁점은 '연속 참여를 어떻게 판단할 것인가?' 이다.

1. group by submission_date, hacker_id
   : 일자별 제출한 'hacker_id'를 구함
2. 세번째 컬럼 sum(1) over(partition by hacker_id order by submission_date)
   : 그 일자까지 참여한 날 수를 구하는 구문이다.
   a해커가 1일, 2일, 3일에 제출했다면 세번째 컬럼도 1, 2, 3값을 가질 것이다.
   b해커는 1일, 3일에만 제출했다면 1일은 1, 3일은 2일 것이다.
   b해커처럼 3일인데도 세번째 컬럼이 3보다 작은 경우는 연속참여하지 않은 것이고, 집계함수에서 제외하면 된다.
3. where submitted_days = submission_date - to_date('2016-03-01','yyyy-mm-dd') + 1
   : 2번에서 설명한 것처럼 연속되지 않는 제출을 제외하는 조건
   (세번째 컬럼이 2일에 2인지, 3일에 3인지, ... 확인)
4. 위 코드들로 연속참여한 unique한 hacker_id들을 구했다. group by와 집계함수로 일자별 총합을 더하여 원하는 답을 구했다.

table2 - 일자별 최대로 제출한 hacker_id, name

select
    *
from
    (
    select
        s.submission_date
        , h.hacker_id
        , h.name
        , count(*) cnt
        , rank() over(partition by s.submission_date order by count(*) desc, h.hacker_id) rnk
    from
        submissions s
        , hackers h
    where
        h.hacker_id = s.hacker_id
    group by
        s.submission_date
        , h.hacker_id
        , h.name
    ) t2
where 
    rnk = 1
;

1. 조인 후 group by s.submission_date, h.hacker_id, h.name
   : 일자, 해커별 제출수 합계를 구했다.
2. rank() over(partition by s.submission_date order by count(*) desc, h.hacker_id) rnk
   : 일자별로partition by submission_date 구한 합계order by count(*)의 순위를 매겼다. 문제에서 요구하듯이 합count(*)이 같다면 hacker_id가 낮은 것을 출력해야하기 때문에 order by 절에 hacker_id도 추가하였다.
3. 순위를 구했으니 서브쿼리 밖에서 순위가 1등인 것만 뽑는다.

이렇게 관건이 되는 두가지 컬럼을 모두 구했으니 조인하여 문제가 요구하는대로 출력하면 끝이다.

# 마치며

window sort가 현업에서 얼마나 사용되는지 궁금한데, DB설계로 대체하기 어려운(위 문제의 경우 '연속참여 여부' 컬럼을 추가하면 서브쿼리 하나가 통째로 필요없다.) 오래되거나 대용량의 DB시스템의 경우 필히 사용될 듯 하고, 반대로 잘 설계된 DB의 경우 구경도 할 수 없을지도 모르겠다.
어쨌든 이 문제를 통해 window sort를 더 직관적으로 사용할 수 있게 된 듯 하다.