[SQL] 해커랭크 15 Days of Learning SQL

STEP 1. SUBMISSION_DATE와 ID, NM의 출력은 간단하다.

SELECT MST.SUBMISSION_DATE
     , (SELECT HACKER_ID
          FROM SUBMISSIONS
         WHERE SUBMISSION_DATE = MST.SUBMISSION_DATE
      GROUP BY HACKER_ID
      ORDER BY COUNT(SUBMISSION_ID) DESC, HACKER_ID LIMIT 1) AS ID
     , (SELECT NAME
          FROM HACKERS
         WHERE HACKER_ID = ID) AS NM
  FROM (SELECT DISTINCT SUBMISSION_DATE FROM SUBMISSIONS) MST
 GROUP BY MST.SUBMISSION_DATE

STEP 2. 아래 쿼리를 이해하자

SELECT COUNT(DISTINCT HACKER_ID)  
  FROM SUBMISSIONS S2  
 WHERE S2.SUBMISSION_DATE = '2016-03-03'
   AND (SELECT COUNT(DISTINCT S3.SUBMISSION_DATE) 
          FROM SUBMISSIONS S3 
         WHERE S3.HACKER_ID = S2.HACKER_ID 
           AND S3.SUBMISSION_DATE < '2016-03-03') = DATEDIFF('2016-03-03', '2016-03-01')

STEP 3. 두 개의 쿼리문을 합치면

SELECT MST.SUBMISSION_DATE
     , (SELECT COUNT(DISTINCT HACKER_ID)  
          FROM SUBMISSIONS S2  
         WHERE S2.SUBMISSION_DATE = MST.SUBMISSION_DATE
           AND (SELECT COUNT(DISTINCT S3.SUBMISSION_DATE) 
                  FROM SUBMISSIONS S3 
                 WHERE S3.HACKER_ID = S2.HACKER_ID 
                   AND S3.SUBMISSION_DATE < MST.SUBMISSION_DATE) = DATEDIFF(MST.SUBMISSION_DATE, '2016-03-01')) AS CNT
     , (SELECT HACKER_ID
          FROM SUBMISSIONS
         WHERE SUBMISSION_DATE = MST.SUBMISSION_DATE
      GROUP BY HACKER_ID
      ORDER BY COUNT(SUBMISSION_ID) DESC, HACKER_ID LIMIT 1) AS ID
     , (SELECT NAME
          FROM HACKERS
         WHERE HACKER_ID = ID) AS NM
  FROM (SELECT DISTINCT SUBMISSION_DATE FROM SUBMISSIONS) MST
 GROUP BY MST.SUBMISSION_DATE

 

해커랭크 15 Days of Learning SQL