해커랭크 15 Days of Learning SQL (재귀 CTE, 연속성 분석)

https://www.hackerrank.com/challenges/15-days-of-learning-sql/problem

15 Days of Learning SQL | HackerRank

---

문제 정의 (영어 -> 한국어 번역 후 문제 파악하기)

15Days of learning SQL 콘테스트일은 2016년 3월 1일 이고 종료일은 2016년 3월 15일 입니다 .

최소한 다음과 같은 작업을 수행한 고유 해커의 총 수를 출력하는 쿼리를 작성하세요.대회 첫날부터 매일 제출된 해커 의 hacker_id 와 이름을 구하고, 매일 가장 많은 제출을 한 해커의 hacker_id 와 이름을 구합니다. 만약 제출한 해커가 두 명 이상인 경우, 가장 낮은 hacker_id 값을 출력합니다 . 쿼리는 이 정보를 대회 기간 동안 매일 날짜별로 정렬하여 출력해야 합니다.

 

문제접근

• 일자, 해커수(매일 연속적으로 제출한), 매일 제출 많이한 해커 id, name
• 조건
  • 연속성 확인 : 대회 첫날부터 매일 제출된 해커 의 hacker_id 와 이름
  • 데일리 max 제출자
    • 데일리로 제출 가장 많이한 해커의 hacker_id 와 이름
    • 데일리 max 동일하면, 해커 이름 기준 rnk 설정
  • 정렬 :  날짜별로 정렬하여 출력

/*
-- 고유 해커의 총 수를 출력하는 쿼리
-- 조건
	--	연속성 확인 : 일자별 연속적으로 제출한 해커수
	--	데일리 max 제출자 : 데일리로 제출 가장 많이 제출, hacker_id 알파벳 순 1개 차출 
	--	정렬 :  날짜별로 정렬하여 출력

-- 테이블
	-- hackers : hacker_id, name
    -- submissions : submission_date, submission_id, hacker_id, score
*/
-- 연속성 확인 : 일자별 연속적으로 제출한 해커수
 with date_rnk as (
    select submission_date,
           dense_rank() over(order by submission_date) as rn 
    from (select distinct submission_date from submissions) sub
)
, date_hacker_rnk as (
    select submission_date, 
           hacker_id,
           dense_rank() over(partition by hacker_id order by submission_date) as rn
    from (select distinct submission_date, hacker_id from submissions) sub
)
, conti_date_h_cnt as (
    select dh.submission_date,
           count(dh.hacker_id) as h_cnt
    from date_hacker_rnk dh
    inner join date_rnk d
        on d.submission_date = dh.submission_date
        and d.rn = dh.rn
    group by dh.submission_date
)
-- 데일리 max 제출자 : 데일리로 제출 가장 많이 제출, hacker_id 알파벳 순 1개 차출 
, day_max_hacker as (
    select submission_date, 
           hacker_id,
           count(submission_id) as sub_cnt,
           row_number() over(
               partition by submission_date
               order by count(submission_id) desc, hacker_id asc
           ) as sub_rn
    from submissions
    group by submission_date, hacker_id
)
select c.submission_date,
       c.h_cnt,
       d.hacker_id,
       h.name
from conti_date_h_cnt c
left join (
    select submission_date, hacker_id, sub_cnt
    from day_max_hacker
    where sub_rn = 1
) d on c.submission_date = d.submission_date
left join hackers h on d.hacker_id = h.hacker_id
order by c.submission_date
;

---

해커별 연속성 확인하는 법

• 중복 제거한 일자에 대한 rnk
• 중복 제거한 해커, 일자에 대해서 해커별 일자 rnk
• 각 일자, rnk 비교