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

📔문제

Julia conducted a 15 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.

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.

---

📝예시

For the following sample input, assume that the end date of the contest was March 06, 2016.

Hackers Table:

Submissions Table:

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

---

🧮분야

• JOIN

---

📃SQL 코드

select g.submission_date,cnt, hi, 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) g,
( select a.submission_date,hc,a.hacker_id,h.name,
    min(a.hacker_id) over (partition by a.submission_date) hi
    from
        (select submission_date,hacker_id, count(*) hc
        from submissions
        group by submission_date, hacker_id) a,
        (select submission_date, max(cnt) m
        from (select hacker_id, submission_date,count(*) cnt
              from submissions
              group by submission_date, hacker_id
              order by submission_date, cnt desc, hacker_id)
        group by submission_date) b,
        (select hacker_id, name
        from hackers)h
    where a.hc=b.m
    and a.submission_date=b.submission_date
    and h.hacker_id= a.hacker_id
    order by submission_date, a.hacker_id) gg
where hi=hacker_id
and g.submission_date=gg.submission_date ;

---

📰출력 결과

---

📂고찰

3월 1일부터 3월 15일까지 하루도 빠짐없이 제출한 사람들 중 최대로 낸 사람을 추출하는 줄 알고, 처음에 계산했는데 다 틀렸었다.

https://yurimyurim.tistory.com/4

찾아보니, 최대로 제출한 사람과 매일 빠짐없이 제출한 사람들을 따로 카운트 하는 것이었다.

진짜 영어로 적혀있어서 이해하기도 어려운데 문제도 프로그래머스보다 너무 꼬여있어서 문제 이해하는 것만 해도 너무 오래걸린다.

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

내가 필요했던 부분은 해당 부분이다.

매일 제출한 사람인지 판단하는 것을 위해선 해당일자까지의 중복을 제외한 제출일 수와 해당일자-시작일자 차이 일수가 같으면 된다.

그리고 나머지는 가장 많이 제출한 사람들의 수 중에 해커 아이디가 가장 작은 사람을 추출하는 것이다.