HackerRank SQL - 15 Days of Learning SQL

Julia conducted a  days of learning SQL contest. The start date of the contest was March 01, 2016 and the end date was March 15, 2016.

Write a query to print total number of unique hackers who made at least  submission each day (starting on the first day of the contest), and find the hacker_id and name of the hacker who made maximum number of submissions each day. If more than one such hacker has a maximum number of submissions, print the lowest hacker_id. The query should print this information for each day of the contest, sorted by the date.

 

Input Format

The following tables hold contest data:

• Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.
• Submissions: The submission_date is the date of the submission, submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, and score is the score of the submission. 

Problem

2016년 3월1일 부터 15일까지 진행되는 SQL대회에서 1일차부터 매일 제출한 hackers의 수와 매일 최대 제출을 한 hacker_id와 name을 추출하라.

단, 일자를 기준으로 정렬하고, 최대 제출 hacker_id가 둘이상이면 id가 낮은 값으로 출력하라.

 

---

Answer1

SELECT Total_Count.submission_date
        , Total_Count.total_number_of_unique_hackers
        , Max_Value.hacker_id
        , H.name
FROM
    (SELECT T.submission_date
           ,COUNT(1) AS total_number_of_unique_hackers
       FROM 
            (SELECT submission_date
                   ,hacker_id
                   ,ROW_NUMBER() OVER(PARTITION BY hacker_id ORDER BY submission_date)
                    - (submission_date - to_date('2016-03-01', 'YYYY-MM-DD')) AS cmp
              FROM (SELECT submission_date, hacker_id
                    FROM Submissions
                GROUP BY submission_date, hacker_id) 
            ORDER BY hacker_id) T
       WHERE T.cmp = 1
    GROUP BY T.submission_date) Total_Count
    ,
    (SELECT T.submission_date
           ,T.hacker_id
           ,ROW_NUMBER() OVER(PARTITION BY T.submission_date ORDER BY CNT DESC) AS IDX
    FROM (SELECT S.submission_date
                   , S.hacker_id
                   , COUNT(1) CNT
            FROM Submissions S
         GROUP BY S.submission_date, S.hacker_id
         ORDER BY S.submission_date, CNT DESC, S.hacker_id) T) Max_Value
    , hackers H
WHERE Total_Count.submission_date = Max_Value.submission_date
   AND Max_Value.hacker_id = H.hacker_id
   AND Max_Value.IDX = 1
;

 

마지막 문제다.

매일 문제를 풀어낸 hackers의 수와 해당 일에 가장 많은 제출을 한 hackers의 정보는 각각 따로 추출 후, 결합해야 한다.

 

 

---

How to solve

매일 제출한 hackers의 수

매일 제출한 hacker의 수를 추출하려면 제출일이 hacker_id를 기준으로 횟수로 묶여야 된다고 생각했다.

그래서 윈도우 함수를 이용해 row_number로 submission_date를 오름차순으로 만들어줬다. 그 전에 한 명의 hacker가 일자별로 구분된 submission_date에서 1번 이상 제출할 수 있으니 FROM절에서 hacker_id의 고유값만 남겨준다.

 

마지막으로, ROW_NUMBER() OVER(PARTITION BY hacker_id ORDER BY submission_date)로 만든 값을 대회 경과일인 (submission_date - to_date('2016-03-01', 'YYYY-MM-DD')으로 빼준다.

 

그럼 유효한 값은 1로 전부 정리가 된다.

SELECT submission_date
       ,hacker_id
       ,ROW_NUMBER() OVER(PARTITION BY hacker_id ORDER BY submission_date)
        - (submission_date - to_date('2016-03-01', 'YYYY-MM-DD')) AS cmp
  FROM (SELECT submission_date, hacker_id
        FROM Submissions
    GROUP BY submission_date, hacker_id) 
ORDER BY hacker_id

 

이를 다시 FROM절에 묶어주고 cmp열의 값이 1이 아닌 값은 모두 소거해준다.

이제 매일 제출한 hackers의 수는 준비가 되었다.

SELECT T.submission_date
       ,COUNT(1) AS total_number_of_unique_hackers
   FROM 
        (SELECT submission_date
               ,hacker_id
               ,ROW_NUMBER() OVER(PARTITION BY hacker_id ORDER BY submission_date)
                - (submission_date - to_date('2016-03-01', 'YYYY-MM-DD')) AS cmp
          FROM (SELECT submission_date, hacker_id
                FROM Submissions
            GROUP BY submission_date, hacker_id) 
        ORDER BY hacker_id) T
   WHERE T.cmp = 1
GROUP BY T.submission_date

 

 

일별로 가장 많이 제출한  hacker_id (name)

일별로 가장 많이 제출한 hacker_id는 가장 처음에 hacker별로 submission_id수를 출력해서 FROM절로 묶는다.

이 쿼리를 다시 row_number로 묶어서 준비한다. 원래는 여기서도 일자별로 조건에 맞는 한 명의 hacker_id만 출력해야되기 때문에 한 번 더 서브쿼리를 씌어야 하지만 그럴 필요가 없다. 어차피 위의 쿼리와 결합을 해야하거든

SELECT T.submission_date
       ,T.hacker_id
       ,ROW_NUMBER() OVER(PARTITION BY T.submission_date ORDER BY CNT DESC) AS IDX
FROM (SELECT S.submission_date
           , S.hacker_id
           , COUNT(1) CNT
        FROM Submissions S
     GROUP BY S.submission_date, S.hacker_id
     ORDER BY S.submission_date, CNT DESC, S.hacker_id) T

 

결합하기

마지막으로 위 쿼리들을 각각 Total_Count, Max_Value라는 테이블로 명명하고 hacker의 name이 필요하니, hackers 테이블도 결합해준다. 진짜 마지막으로 까먹지말고 Max_Value에서 한 명만 출력하도록 조건을 추가한다.

SELECT Total_Count.submission_date
        , Total_Count.total_number_of_unique_hackers
        , Max_Value.hacker_id
        , H.name
FROM
    (SELECT T.submission_date
           ,COUNT(1) AS total_number_of_unique_hackers
       FROM 
            (SELECT submission_date
                   ,hacker_id
                   ,ROW_NUMBER() OVER(PARTITION BY hacker_id ORDER BY submission_date)
                    - (submission_date - to_date('2016-03-01', 'YYYY-MM-DD')) AS cmp
              FROM (SELECT submission_date, hacker_id
                    FROM Submissions
                GROUP BY submission_date, hacker_id) 
            ORDER BY hacker_id) T
       WHERE T.cmp = 1
    GROUP BY T.submission_date) Total_Count
    ,
    (SELECT T.submission_date
           ,T.hacker_id
           ,ROW_NUMBER() OVER(PARTITION BY T.submission_date ORDER BY CNT DESC) AS IDX
    FROM (SELECT S.submission_date
                   , S.hacker_id
                   , COUNT(1) CNT
            FROM Submissions S
         GROUP BY S.submission_date, S.hacker_id
         ORDER BY S.submission_date, CNT DESC, S.hacker_id) T) Max_Value
    , hackers H
WHERE Total_Count.submission_date = Max_Value.submission_date
   AND Max_Value.hacker_id = H.hacker_id
   AND Max_Value.IDX = 1
;

 

 

[ 23-02-23 : 추가 내용 ] 

이런저런 핑계로 질질 끌다가 드디어 다 풀었다. 사실 실무만 잘하면 되지 하는 생각도 하다가, 1달 반 전, 난생 처음 쿼리테스를 보면서 맨날 보던 테이블, 스키마가 아니라 완전 새로운 정보도 빨리 이해하고 쿼리를 도출할 수 있는 사람이 되고 싶었다. 

 

레퍼런스는 최소화하고 하나하나 푸는데 너무 오래 걸렸지만, 그래도 너무 즐거웠다.