[해커랭크] SQL(hard) - 15 Days of Learning SQL 문제 풀이

MS SQL Server를 사용합니다.

테이블 간 관계와 문제가 요구하는 output은 이미지와 같습니다.

유의사항

• mysql의 경우, with 구문을 사용할 수 없습니다 : 해커랭크에서는 8.0 이하 버전을 지원합니다.
• 각 날짜에 최소 1번 이상 submission한 해커란, 기준일자까지 매일매일 submission한 해커를 말합니다.
  • description만 보고서는 이 부분을 제대로 이해하기 어려웠습니다. 이것 때문에 시간을 많이 잡아먹었네요

 

인라인 뷰 생성(with)

with cte as (
    select s.hacker_id, h.name, s.submission_date, count(*) as submissions 
    from submissions s left join hackers h
    on s.hacker_id = h.hacker_id
    group by s.hacker_id, h.name, s.submission_date
), cte2 as (
    select hacker_id, submission_date, name, submissions, row_number() over(partition by submission_date order by submissions desc, hacker_id) as rn
    from cte
), cte3 as (
    select hacker_id, submission_date, row_number() over (partition by hacker_id order by submission_date) as rn
    from cte
), cte4 as (
    select submission_date, count(distinct hacker_id) as hackers
    from cte3
    where rn = day(submission_date)
    group by submission_date
)

• cte : 해커별 날짜별 submission 수를 집계합니다.
• cte2 : 날짜별 submission 수에 따른 행 번호를 계산합니다.
  • submission 수와 hacker_id를 기준으로 정렬합니다.
• cte3 : 해커별 행 번호를 계산합니다.
  • 날짜를 기준으로 정렬합니다.
• cte4 : 날짜별 submission 수를 집계합니다.
  • 단, cte3의 해커별 행 번호가 해당 날짜와 일치하는 경우만 필터링합니다.
  • 예를 들어, 해커가 3일 연속 submission한 경우 2016년 3월 3일의 행 번호는 3이 됩니다. 반면 해커가 2일만 연속 submission을 하고, 하루 쉰 뒤 다시 submission을 한 경우 2016년 3월 3일의 행 번호는 존재하지 않습니다. 대신 행 번호가 3인 날짜는 2016년 3월 4일이 됩니다.
  • 따라서 행 번호와 해당 날짜가 일치하는 경우만 필터링하면  기준일자까지 매일매일 submission한 해커를 선택하게 됩니다. 이는 데이터의 시작 날짜가 2016년 3월 1일로 고정되어 있기 때문에 가능합니다.

 

최종 output 만들기

select cte4.submission_date, cte4.hackers, cte2.hacker_id, cte2.name
from cte4 left join cte2
on cte4.submission_date = cte2.submission_date
where cte2.rn = 1
order by cte4.submission_date

• cte4와 cte2를 submission_date에 따라 조인합니다.
• submission date, 기준일자까지 매일 submission한 해커 수, 해커의 id와 이름을 출력합니다
• cte2의 행 번호가 1인 경우만 필터링합니다. 이를 통해 해당 날짜에 가장 많이 submission한 해커를 필터링할 수 있습니다.
• submission date 순으로 정렬합니다.