[HackerRank] 15 Days of Learning SQL 풀이 (Oracle)

문제

15 Days of Learning SQL | HackerRank

15 Days of Learning SQL | HackerRank

문제 해설

2016년 3월 1일부터 2016년 3월 15일까지 15일간의 SQL 대회를 진행했습니다.

매일 1회 이상 제출한 해커의 중복을 제외한 총 인원 수와, 매일 최대 제출 수를 기록한 해커의 ID와 이름을 출력합니다. 2명 이상의 해커가 최대 제출 수를 갖고 있는 경우 가장 낮은 해커의 ID를 출력합니다.

해당 정보에 대해 각 일자별로 정렬해서 출력해야 합니다.

매일 1회 이상 제출한 해커는 3월 2일일 경우 1일과 2일에 모두 제출한 해커, 3월 3일일 경우 1, 2, 3일에 모두 제출한 해커입니다.

Hackers 테이블

Submissions 테이블

풀이

처음에 문제를 잘못 이해해서 매일 1회 이상 제출한 해커를 구하고 그중에 최대 제출 수를 기록한 해커를 구하는 바람에 테스트 케이스에서 실패했었는데요.

최대 제출 수를 기록한 해커는 선제 조건 없이 별도로 구해야 한다는 것을 알게 되었습니다.

즉, 각 일자별로 매일 1회 이상 제출한 해커의 총 인원수, 최대 제출 수를 기록한 해커를 따로 구해야 합니다.

 

먼저 각 일자별로 매일 1회 이상 제출한 해커의 총 인원 수를 구합니다.

매일 제출한 해커인지 판단하기 위해서는

해커별로 해당일자까지의 중복을 제외한 제출일 건수가,

해당일자와 시작일자(2016-03-01)까지의 차이 일수와 같아야 합니다.

WHERE 절의 서브쿼리로 구현해보았습니다.

SELECT SUBMISSION_DATE
     , COUNT(DISTINCT HACKER_ID) CNT
  FROM SUBMISSIONS S
 WHERE (SELECT COUNT(DISTINCT SUBMISSION_DATE)
          FROM SUBMISSIONS SS
         WHERE SS.SUBMISSION_DATE < S.SUBMISSION_DATE
           AND SS.HACKER_ID = S.HACKER_ID
       ) = (S.SUBMISSION_DATE - TO_DATE('2016-03-01'))
 GROUP BY SUBMISSION_DATE

 

그 다음 각 일자별로 최대 제출 수를 기록한 해커를 구하려고 합니다.

일자별, 해커별(GROUP BY 일자, 해커)로 제출 수를 먼저 구합니다.

ROW_NUMBER 함수를 통해 일자별로(PARTITION BY 일자) 제출 수 내림차순, ID 오름차순으로 랭킹을 구합니다.

랭킹이 1인 데이터가 최대 제출 수를 기록한 해커가 될 것입니다.

SELECT SUBMISSION_DATE
     , HACKER_ID
     , ROW_NUMBER() OVER(PARTITION BY SUBMISSION_DATE ORDER BY CNT DESC, HACKER_ID) RN
  FROM (
        SELECT SUBMISSION_DATE
             , HACKER_ID
             , COUNT(1) CNT
          FROM SUBMISSIONS S
         GROUP BY SUBMISSION_DATE, HACKER_ID
       )

 

앞서 구한 두 개의 쿼리를 제출일로 JOIN 해주고, 해커 이름을 구하기 위해 HACKER 테이블도 JOIN 해줬습니다.

최종 쿼리입니다.

SELECT A.SUBMISSION_DATE, A.CNT, B.HACKER_ID, C.NAME
  FROM (
        SELECT SUBMISSION_DATE
             , COUNT(DISTINCT HACKER_ID) CNT
          FROM SUBMISSIONS S
         WHERE (SELECT COUNT(DISTINCT SUBMISSION_DATE)
                  FROM SUBMISSIONS SS
                 WHERE SS.SUBMISSION_DATE < S.SUBMISSION_DATE
                   AND SS.HACKER_ID = S.HACKER_ID
               ) = (S.SUBMISSION_DATE - TO_DATE('2016-03-01'))
         GROUP BY SUBMISSION_DATE
       ) A
     , (SELECT SUBMISSION_DATE
             , HACKER_ID
             , ROW_NUMBER() OVER(PARTITION BY SUBMISSION_DATE ORDER BY CNT DESC, HACKER_ID) RN
          FROM (
                SELECT SUBMISSION_DATE
                     , HACKER_ID
                     , COUNT(1) OVER(PARTITION BY SUBMISSION_DATE, HACKER_ID) CNT
                  FROM SUBMISSIONS S
               )
       ) B
     , HACKERS C
 WHERE B.RN = 1
   AND A.SUBMISSION_DATE = B.SUBMISSION_DATE
   AND B.HACKER_ID = C.HACKER_ID
;

 

결과