[Oracle] HackerRank 문제 정리 - 6

15 Days of Learing SQL

테이블:

Hackers:

Submissions:

Sample Input:

Hackers Table

Submissions Table

Sample Output:

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

 

조건:

Julia는 SQL을 배우는 날들을 진행했습니다. 대회 시작일은 2016년 3월 1일, 종료일은 2016년 3월 15일이다.

매일 최소 제출자 수를 출력하는 쿼리를 작성하고(대회 첫날부터), 매일 최대 제출자 수를 기록한 해커의 hacker_id와 이름을 찾습니다. 둘 이상의 해커가 최대 제출 수를 가질 경우 가장 낮은 hacker_id를 인쇄합니다. 쿼리는 날짜별로 정렬된 콘테스트의 각 날짜에 대해 이 정보를 인쇄해야 합니다.

WITH
DAYSASLEVELS as 
    (
    select Submission_date, 
        count(distinct Hacker_id) as NumHackers
     from SUBMISSIONS
     group by Submission_date
     start with Submission_date='2016-03-01'
     connect by 
         prior to_date(Submission_date,'yyyy-mm-dd') 
             = to_date(Submission_date,'yyyy-mm-dd')-1 
        and        prior Hacker_id=Hacker_id 
        and to_date(Submission_date,'yyyy-mm-dd')
             <= to_date('2016-03-15','yyyy-mm-dd')
    )
   ,
/* returns: Submission_date, Hacker_id, numberOfHacks */
/*           for each hacker on each day */
    MULTIHACKERS as
    (
    select Submission_date, 
        Hacker_id, 
        count(Submission_id) as NumHacks
     from SUBMISSIONS
     where to_date(Submission_date,'yyyy-mm-dd') 
        between to_date('2016-03-01','yyyy-mm-dd') 
         and to_date('2016-03-15','yyyy-mm-dd')
     group by Submission_date, Hacker_id
     having count(Submission_id)>0
    ),
/* returns: Submission_date, maxNumberOfHacks  on that day   */
    MOSTHACKS as
    (
    select Submission_date as Subdate, 
        max(NumHacks) as MaxHacks
     from MULTIHACKERS
     group by Submission_date
    ),
/* returns: date, numHackersWithSubEveryDaySoFar,  */
/*          smallestHackerIdWithMaxhacks           */
    SOLNBYID as
    (
select MH1.Submission_date as Sdate, NumHackers, min(MH2.Hacker_id) as WinnerId
from   MULTIHACKERS MH1 inner join MULTIHACKERS MH2 on MH1.Submission_date=MH2.Submission_date inner join MOSTHACKS on MH2.NumHacks=maxHacks
and    MH2.Submission_date=MOSTHACKS.Subdate inner join DAYSASLEVELS on MH1.Submission_date=DAYSASLEVELS.Submission_date
group by MH1.Submission_date, NumHackers
    )
/*  tack on the Name of the winning Hacker_id */
 select Sdate, NumHackers, H.Hacker_id, H.Name
  from (SOLNBYID inner join HACKERS H on H.Hacker_id=WinnerId)
  order by to_date(Sdate,'yyyy-mm-dd');

쿼리 해석

여기서 가장 중점적으로 처리 해줘야 할 것은 어떻게 맨처음부터 계속 제출한 사람을 찾느냐 입니다.

이 때 사용해야할 것이 Start with connect by prior 절이다.
이것을 사용하는 서브쿼리는 DAYSASLEVELS 로, Submissions 테이블에서 날짜가 하루이전과 hacker_id가 같은 걸 묶어서 이전날짜에 같은 hacker_id가 있는지 판단하여 숫자를 구한다. 이것을 select할 때 distinct해서 유니크한 그 수를 구한다.

그 이후에는 MULTIHACKERS 서브쿼리절에서는 3월 1일부터 3월 15일의 제출일자에서 제출일자와 해커 아이디를 묶어주어 제출한 숫자가 0보다 큰 것의 수를 구해준다.

다음으로는 MOSTHACKS 서브쿼리로 위의 MULTIHACKERS 테이블에서 같은 날짜중에 가장 큰 것을 보여주는 쿼리를 만들어준다.

마지막으로 SOLNBYID 서브쿼리는 위에 구한 값들을 조인해주는 쿼리로 Daysaslevels의 NUMHACK와 같은 날짜인 것을 가져와주고  MOSTHACKS의 날짜와 수와 Hacker_id를 연결시켜주고

마지막으로는 다 같이 돌려서 날짜, NumHackers, id, 이름과 날짜 순으로 뽑아서 출력해준다.