SQL 코드카타 180 - 15 Days of Learning SQL

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

15 Days of Learning SQL | HackerRank

 

Column	Type
hacker_id	Integer
name	String

 

Hackers 테이블:

• hacker_id : 해커의 id
• name : 해커의 이름

 

Column	Type
submission_date	Date
submission_id	Integer
hacker_id	Integer
score	Integer

 

Submissions 테이블:

• submission_date : 제출 날짜
• submission_id : 제출물의 id
• hacker_id : 제출한 해커의 id
• score : 제출물의 점수

 

줄리아는 15일 동안 SQL 학습 콘테스트를 진행했습니다. 콘테스트의 시작 날짜는 2016년 3월 1일이고, 종료 날짜는 2016년 3월 15일입니다.

매일 최소 1회 제출한 고유 해커 수와 각 날마다 최대 제출 수를 기록한 해커의 hacker_id와 이름을 출력하는 쿼리를 작성하세요. 만약 여러 해커가 동일한 최대 제출 수를 기록했다면, hacker_id가 가장 낮은 해커를 출력하세요. 쿼리는 콘테스트의 각 날에 대한 정보를 날짜 순으로 출력해야 합니다.

 

SELECT 
    S1.SUBMISSION_DATE,
    (
        SELECT COUNT(DISTINCT S2.HACKER_ID)
        FROM SUBMISSIONS S2
        WHERE S2.SUBMISSION_DATE = S1.SUBMISSION_DATE
          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')
    ) AS HACKER_COUNT,
    (
        SELECT S2.HACKER_ID
        FROM SUBMISSIONS S2
        WHERE S2.SUBMISSION_DATE = S1.SUBMISSION_DATE
        GROUP BY S2.HACKER_ID
        ORDER BY COUNT(S2.SUBMISSION_ID) DESC, S2.HACKER_ID
        LIMIT 1
    ) AS TMP,
    (
        SELECT NAME
        FROM HACKERS
        WHERE HACKER_ID = TMP
    ) AS HACKER_NAME

FROM
    (
        SELECT DISTINCT SUBMISSION_DATE
        FROM SUBMISSIONS
    ) S1
GROUP BY 
    S1.SUBMISSION_DATE;

 

며칠동안 붙들고 있었지만 결국 제대로 풀지 못했다... 결국 정답은 인터넷에 검색을 해서 찾아보았고 코드를 정리해보면서 마무리해보려고 한다...

 

SELECT 
    S1.SUBMISSION_DATE
FROM
    (
        SELECT DISTINCT SUBMISSION_DATE
        FROM SUBMISSIONS
    ) S1
GROUP BY 
    S1.SUBMISSION_DATE;

 

SUBMISSIONS 테이블을에서 submission_date열의 데이터의 중복을 제거하여 출력한다. 그리고 해당 쿼리를 서브쿼리화하여 submission_date를 기준으로 그룹화해서 다시 submission_date를 출력하도록 하고 위의 쿼리에 출력할 데이터를 추가한다.

 

-- SELECT 
--     S1.SUBMISSION_DATE,
    (
        SELECT COUNT(DISTINCT S2.HACKER_ID)
        FROM SUBMISSIONS S2
        WHERE S2.SUBMISSION_DATE = S1.SUBMISSION_DATE
          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')
    ) AS HACKER_COUNT
-- FROM
--     (
--         SELECT DISTINCT SUBMISSION_DATE
--         FROM SUBMISSIONS
--     ) S1
-- GROUP BY 
--     S1.SUBMISSION_DATE;

 

첫번째 서브쿼리, S1.SUBMISSION_DATE에 대해서 각각 날짜별 고유한 해커의 수를 센다. 

 

-- SELECT 
--     S1.SUBMISSION_DATE,
--     (
--         SELECT COUNT(DISTINCT S2.HACKER_ID)
--         FROM SUBMISSIONS S2
--         WHERE S2.SUBMISSION_DATE = S1.SUBMISSION_DATE
--           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')
--     ) AS HACKER_COUNT,
    (
        SELECT S2.HACKER_ID
        FROM SUBMISSIONS S2
        WHERE S2.SUBMISSION_DATE = S1.SUBMISSION_DATE
        GROUP BY S2.HACKER_ID
        ORDER BY COUNT(S2.SUBMISSION_ID) DESC, S2.HACKER_ID
        LIMIT 1
    ) AS TMP
-- FROM
--     (
--         SELECT DISTINCT SUBMISSION_DATE
--         FROM SUBMISSIONS
--     ) S1
-- GROUP BY 
--     S1.SUBMISSION_DATE;

 

두번째 서브쿼리, S1.SUBMISSION_DATE의 날짜에서 가장 제출을 많이 한 해커의 ID를 출력한다. 그룹화된 쿼리를 내림차순으로 정렬한 뒤 LIMIT를 이용해서 가장 큰 데이터만 출력하도록 한다.

 

-- SELECT 
--     S1.SUBMISSION_DATE,
--     (
--         SELECT COUNT(DISTINCT S2.HACKER_ID)
--         FROM SUBMISSIONS S2
--         WHERE S2.SUBMISSION_DATE = S1.SUBMISSION_DATE
--           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')
--     ) AS HACKER_COUNT,
--     (
--         SELECT S2.HACKER_ID
--         FROM SUBMISSIONS S2
--         WHERE S2.SUBMISSION_DATE = S1.SUBMISSION_DATE
--         GROUP BY S2.HACKER_ID
--         ORDER BY COUNT(S2.SUBMISSION_ID) DESC, S2.HACKER_ID
--         LIMIT 1
--     ) AS TMP,
    (
        SELECT NAME
        FROM HACKERS
        WHERE HACKER_ID = TMP
    ) AS HACKER_NAME
-- FROM
--     (
--         SELECT DISTINCT SUBMISSION_DATE
--         FROM SUBMISSIONS
--     ) S1
-- GROUP BY 
--     S1.SUBMISSION_DATE;

 

세번째 서브쿼리, TMP쿼리에서 해커의 이름을 가져와서 출력한다.

 

일단 정리는 이렇게 끝이 났는데... 아직 제대로 이해한지는 모르겠다. 그동안은 서브쿼리를 활용하면서 보통은 from 구문에 사용을 했고 정말 가끔씩 where구문에 사용을 했기때문이다. select 구문에 바로 사용을 하는 것은 처음해보는 일이라 아직 쿼리의 흐름을 잘 모르겠다...

 

SELECT 
    S1.SUBMISSION_DATE,
    
    (
        SELECT COUNT(DISTINCT S2.HACKER_ID)
        FROM SUBMISSIONS S2
        WHERE S2.SUBMISSION_DATE = S1.SUBMISSION_DATE
          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')
    ) AS HACKER_COUNT,

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

    (
        SELECT NAME
        FROM HACKERS
        WHERE HACKER_ID = TMP
    ) AS HACKER_NAME

FROM
    (
        SELECT DISTINCT SUBMISSION_DATE
        FROM SUBMISSIONS
    ) S1

GROUP BY 
    S1.SUBMISSION_DATE;