MySQL 해커랭크 | 15 Days of Learnig SQL (해커랭크 Hard 문제풀이, SELECT문 서브쿼리)

 

문제

https://www.hackerrank.com/challenges/15-days-of-learning-sql/problem?isFullScreen=true

15 Days of Learning SQL | HackerRank

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 1 submission each day (starting on the first day of the contest)  _적어도 1개는 제출한 고유 해커 수 (콘테스트를 시작한 이후)
• 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.

# 문제 해석

• contest → 2016년 3월 1일부터 15일까지 진행
• 콘테스트 시작 이후 각 날짜마다 적어도 1개의 제출이 있었던 총 고유 해커 수 출력
• 각 날짜마다 가장 많이 제출한 해커 아이디와 해커 이름 출력
  • 만약 가장 많이 제출한 해커가 한 명보다 많으면 해커 아이디가 가장 낮은 레코드 출력
• 쿼리문은 콘테스트 날짜 순으로 정렬

 

테이블

Hackers	Submissions

 

풀이과정

# SELECT 조회 결과물

• 3/1 ~3/ 15일까지의 고유 날짜
• 각 날짜에 제출이 있었던 고유 해커 수
  • 단, 시작일부터 매일 적어도 1개씩 제출한 해커만 해당
• 해당 날짜에 가장 많이 제출한 해커의 아이디
  • 첫 날부터 제출했는지 여부는 상관없음
  • 제출 수가 모두 같으면 해커 아이디의 숫자가 가장 낮은 사람의 아이디
• 해당 날짜에 가장 많이 제출한 해커의 이름

 

# 시작일부터 매일 적어도 1개씩 제출한 해커의 고유 수

• WHERE 절의 첫 번째 조건을 통해
  3/1 ~ 3/15 각 날짜마다의 결과 값을 출력할 수 있게 해줌
• WHERE 절의 두 번째 조건을 통해
  [각 해커의 고유 제출일자 카운트 값 = 시작일부터 해당 날짜까지의 경과일자] 인 경우의 해커 아이디만 필터링해줌

해커명	2016-03-01	2016-03-02	3/2 제출 고유 해커 수	2016-03-03	3/3 제출 고유 해커 수
H1	제출 1개	제출 3개	2명 (H1과 H2모두 시작일부터 모두 제출)	제출 0개	1명 (H2만 시작일부터 모두 제출)
H2	제출 1개	제출 1개		제출 1개

• 예를 들어,  해커 H1과 H2가 있고, 3/1일부터 3/3일까지 시간이 흘러갈 때
  시작일부터 꾸준히 해당 일자에 제출이 있었던 고유 해커 수를 세는 방법은 
  해커 H1과 H2가 제출한 날짜의 고유 카운트 값이 시작일부터의 경과일자와 같아야 함
  • H1의 제출일자 고유 카운트 값은 3/1, 3/2 → 두 번 제출해서 2
    ≠ 현재 시점은 3/3일이고,  시작일인 3/1일부터의 경과일자는 3
  • H2의 제출일자 고유 카운트 값은 3/1, 3/2, 3/3 → 세 번 제출해서 3
    = 현재 시점은 3/3일이고,  시작일인 3/1일부터의 경과일자는 3

SELECT SUBMISSION_DATE,
       (SELECT COUNT(DISTINCT HACKER_ID)
        FROM SUBMISSIONS S2
        WHERE S2.SUBMISSION_DATE = S1.SUBMISSION_DATE      -- WHERE절 첫 번째 조건
        AND (SELECT COUNT(DISTINCT S3.SUBMISSION_DATE)     -- WHERE절 두 번째 조건
             FROM SUBMISSIONS S3
             WHERE S3.HACKER_ID = S2.HACKER_ID
             AND S3.SUBMISSION_DATE < S1.SUBMISSION_DATE)
             = DATEDIFF(S1.SUBMISSION_DATE, '2016-03-01'))
FROM (SELECT DISTINCT SUBMISSION_DATE FROM SUBMISSIONS) S1

 

 

 

# 해당 날짜에 가장 많이 제출한 해커의 아이디

• 해커 아이디로 그룹화한 뒤 각 해커가 제출한 개수 카운트
• 제출 개수 카운트 값으로 내림차순,  제출 개수가 같은 경우엔 해커 아이디 오름차순 정렬시켜주기
• LIMIT 1로 제일 위에 출력되는 해커 아이디 값만 조회되도록 하기

SELECT SUBMISSION_DATE,
       (SELECT HACKER_ID
        FROM SUBMISSIONS S2
        WHERE S2.SUBMISSION_DATE = S1.SUBMISSION_DATE 
        GROUP BY HACKER_ID
        ORDER BY COUNT(SUBMISSION_ID) DESC, HACKER_ID LIMIT 1) AS H_ID
FROM (SELECT DISTINCT SUBMISSION_DATE FROM SUBMISSIONS) S1

 

 

# 해당 날짜에 가장 많이 제출한 해커의 이름

• 위에서 구한 '가장 많이 제출한 해커의 아이디'를 활용
• '가장 많이 제출한 해커의 아이디'와 같은 '해커의 이름'만 조회되도록 하기

SELECT SUBMISSION_DATE,
       (SELECT HACKER_ID
        FROM SUBMISSIONS S2
        WHERE S2.SUBMISSION_DATE = S1.SUBMISSION_DATE 
        GROUP BY HACKER_ID
        ORDER BY COUNT(SUBMISSION_ID) DESC, HACKER_ID LIMIT 1) AS H_ID,
        (SELECT NAME FROM HACKERS WHERE HACKER_ID = H_ID)
FROM (SELECT DISTINCT SUBMISSION_DATE FROM SUBMISSIONS) S1

 

 

# 정답

• 이제까지 만든 모든 쿼리문을 하나로 합하면 끝!

SELECT SUBMISSION_DATE,
       (SELECT COUNT(DISTINCT HACKER_ID)
        FROM SUBMISSIONS S2
        WHERE S2.SUBMISSION_DATE = S1.SUBMISSION_DATE -- 3/1 ~ 3/15일 시점 맞추기
        AND (SELECT COUNT(DISTINCT S3.SUBMISSION_DATE)  -- 각 해커의 고유 제출일자 카운트값
             FROM SUBMISSIONS S3
             WHERE S3.HACKER_ID = S2.HACKER_ID
             AND S3.SUBMISSION_DATE < S1.SUBMISSION_DATE)
             = DATEDIFF(S1.SUBMISSION_DATE, '2016-03-01')), -- = 3/1일부터 해당 날짜까지의 경과일자
       (SELECT HACKER_ID
        FROM SUBMISSIONS S2
        WHERE S2.SUBMISSION_DATE = S1.SUBMISSION_DATE -- 3/1 ~ 3/15일 시점 맞추기
        GROUP BY HACKER_ID
        ORDER BY COUNT(SUBMISSION_ID) DESC, HACKER_ID LIMIT 1) AS H_ID,
       (SELECT NAME FROM HACKERS WHERE HACKER_ID = H_ID)
FROM (SELECT DISTINCT SUBMISSION_DATE FROM SUBMISSIONS) S1