HackerRank: SQL 풀이 60 (완)

60. Advanced Join: 15 Days of Learning SQL

3시간 걸린 역대급 문제...

1. 첫번째 시도한 오답 쿼리...

WITH CTE_ID_CNT AS (
    SELECT submission_date, hacker_id, COUNT(submission_id) AS SUB_CNT
    FROM Submissions
    GROUP BY submission_date, hacker_id
),
CTE_RK AS (
    SELECT submission_date, hacker_id, RANK () OVER (PARTITION BY submission_date ORDER BY SUB_CNT DESC, hacker_id ASC) AS RK
    FROM CTE_ID_CNT
),
CTE_RK1 AS (
    SELECT submission_date, hacker_id, RK
    FROM CTE_RK
    WHERE RK = 1
),
CTE_ALL_DAY AS (
    SELECT hacker_id
    FROM Submissions
    GROUP BY hacker_id
    HAVING COUNT(DISTINCT submission_date) = 15
),
CTE_UNIQUE AS (
    SELECT submission_date, COUNT(DISTINCT hacker_id) AS ID_CNT
    FROM Submissions
    WHERE hacker_id IN (SELECT hacker_id FROM CTE_ALL_DAY)
    GROUP BY submission_date
)
SELECT CR1.submission_date,
       COALESCE(CU.ID_CNT, 0) AS ID_CNT, 
       CR1.hacker_id, 
       H.name
FROM CTE_RK1 CR1
LEFT JOIN CTE_UNIQUE CU ON CR1.submission_date = CU.submission_date
INNER JOIN Hackers H ON CR1.hacker_id = H.hacker_id
ORDER BY CR1.submission_date;

이렇게 하면 뭐가 문제인가요?

1. 문제의 특성상 대회가 15일 동안 진행되었다면, 모든 참가자는 15일 동안 연속으로 제출해야 됨.
2. 그런데 CTE_ALL_DAY에서 15일 연속 제출한 사람들만 필터링하므로, 만약 한 명이라도 하루를 빠졌다면 그 사람은 카운트되지 않음
3. 결과적으로 15일 내내 제출한 사람들만 남게 되는 대참사
4. 따라서 CTE_UNIQUE에서는 이 15일 연속 제출한 사람들의 수를 세는데, 모든 사람이 매일 제출했다면 매일 동일한 사람들이 제출한 것이므로 모든 날짜에 대해 동일한 숫자(35)가 나오게 됨.

2. 1차 수정 쿼리 (이것도 오답)

WITH CTE_ID_CNT AS (
    SELECT submission_date, hacker_id, COUNT(submission_id) AS SUB_CNT
    FROM Submissions
    GROUP BY submission_date, hacker_id
),
CTE_RK AS (
    SELECT submission_date, hacker_id, RANK () OVER (PARTITION BY submission_date ORDER BY SUB_CNT DESC, hacker_id ASC) AS RK
    FROM CTE_ID_CNT
),
CTE_RK1 AS (
    SELECT submission_date, hacker_id, RK
    FROM CTE_RK
    WHERE RK = 1
),
CTE_UNIQUE AS (
    SELECT submission_date, COUNT(DISTINCT hacker_id) AS ID_CNT
    FROM Submissions
    GROUP BY submission_date
)
SELECT CR1.submission_date,
       COALESCE(CU.ID_CNT, 0) AS ID_CNT, 
       CR1.hacker_id, 
       H.name
FROM CTE_RK1 CR1
LEFT JOIN CTE_UNIQUE CU ON CR1.submission_date = CU.submission_date
INNER JOIN Hackers H ON CR1.hacker_id = H.hacker_id
ORDER BY CR1.submission_date;

이것도 틀림

이유:
원래 문제 의도는 연속으로 제출한 해커의 수를 세어야 하는데, 현재는 단순히 각 날짜별 제출한 해커의 수를 세고 있음;;; (아니 그런데 이거 문제 번역이 좀 이상했음!!!! 진짜.ㅠ)
어쨌든 의도대로 풀려면
1) 첫째 날에는 모든 제출자 수를 세고
2) 둘째 날에는 1-2일 연속 제출한 사람 수를 세고
3) 셋째 날에는 1-3일 연속 제출한 사람 수를 세는 식으로
쿼리를 짜야함.

3. 정답 쿼리 (저는 틀렸습니다~ ㅠㅠ )

WITH CTE_ID_CNT AS (
    SELECT submission_date, hacker_id, COUNT(submission_id) AS SUB_CNT
    FROM Submissions
    GROUP BY submission_date, hacker_id
),
CTE_RK AS (
    SELECT submission_date, hacker_id, RANK () OVER (PARTITION BY submission_date ORDER BY SUB_CNT DESC, hacker_id ASC) AS RK
    FROM CTE_ID_CNT
),
CTE_RK1 AS (
    SELECT submission_date, hacker_id, RK
    FROM CTE_RK
    WHERE RK = 1
),
CTE_CONSECUTIVE AS (
    SELECT s1.submission_date, COUNT(DISTINCT s1.hacker_id) as ID_CNT
    FROM Submissions s1
    WHERE EXISTS (
        SELECT 1
        FROM Submissions s2
        WHERE s2.hacker_id = s1.hacker_id
        AND s2.submission_date <= s1.submission_date
        GROUP BY s2.hacker_id
        HAVING COUNT(DISTINCT s2.submission_date) = DATEDIFF(day, '2016-03-01', s1.submission_date) + 1
    )
    GROUP BY s1.submission_date
)
SELECT CR1.submission_date,
       COALESCE(CC.ID_CNT, 0) AS ID_CNT, 
       CR1.hacker_id, 
       H.name
FROM CTE_RK1 CR1
LEFT JOIN CTE_CONSECUTIVE CC ON CR1.submission_date = CC.submission_date
INNER JOIN Hackers H ON CR1.hacker_id = H.hacker_id
ORDER BY CR1.submission_date;

진짜 괜히 HackerRank 하드모드 쿼리가 아니다...

이 부분의 쿼리에서 연속된 제출 날짜를 찾는 과정을 한번 제대로 체크해보자...

테이블 예시 (Submissions)

우선 Submissions 테이블의 예시 데이터를 생각해보자...
각 행은 특정 해커의 제출 정보를 나타낸다...

submission_id	hacker_id	submission_date
1	101	2016-03-01
2	101	2016-03-02
3	101	2016-03-03
4	102	2016-03-01
5	102	2016-03-02
6	103	2016-03-01
7	103	2016-03-03
8	103	2016-03-04

이 데이터를 기반으로 쿼리를 어떻게 해석하는지 함 보자...

---

1. EXISTS 서브쿼리 이해하기

이 서브쿼리는 각 해커가 제출한 날짜가 연속적으로 존재하는지를 확인하는 역할을 한다.

WHERE EXISTS (
    SELECT 1
    FROM Submissions s2
    WHERE s2.hacker_id = s1.hacker_id
    AND s2.submission_date <= s1.submission_date
    GROUP BY s2.hacker_id
    HAVING COUNT(DISTINCT s2.submission_date) = DATEDIFF(day, '2016-03-01', s1.submission_date) + 1
)

이 서브쿼리는 각 s1.submission_date마다 해당 해커(s1.hacker_id)가 이전에 제출한 모든 날짜들을 찾아서 그 날짜가 연속적으로 존재하는지를 확인한다.

1. s2.hacker_id = s1.hacker_id: s1과 같은 hacker_id를 가진 s2의 모든 기록을 찾고
2. s2.submission_date <= s1.submission_date: s1.submission_date와 같은 날짜 또는 이전 날짜를 선택한 후에
3. COUNT(DISTINCT s2.submission_date): 이 날짜들 중 중복되지 않은 날짜를 세고, 이를 연속 날짜의 개수로 해석한다.
4. HAVING COUNT(DISTINCT s2.submission_date) = DATEDIFF(day, '2016-03-01', s1.submission_date) + 1: 2016-03-01부터 s1.submission_date까지의 날짜 수와 같은 수의 연속된 날짜가 있는지를 확인

2. 이 부분이 어떻게 동작하는지 확인하기

2.1. 첫 번째 해커 hacker_id = 101의 submission_date = 2016-03-03 예시

• s1.submission_date = 2016-03-03일 때, s1.hacker_id = 101의 이전 날짜인 2016-03-01과 2016-03-02를 확인한다.

• s2는 hacker_id = 101이고 submission_date <= 2016-03-03인 모든 날짜를 찾는다.

  SELECT 1
  FROM Submissions s2
  WHERE s2.hacker_id = 101
  AND s2.submission_date <= '2016-03-03'
  GROUP BY s2.hacker_id
  HAVING COUNT(DISTINCT s2.submission_date) = DATEDIFF(day, '2016-03-01', '2016-03-03') + 1

• DATEDIFF(day, '2016-03-01', '2016-03-03') + 1은 3일. 즉, 101 해커는 2016-03-01, 2016-03-02, 2016-03-03에 모두 제출한 연속된 3일의 기록을 가진다.

• 따라서 HAVING COUNT(DISTINCT s2.submission_date) = 3이 참이 되어 EXISTS 절은 참이 된다..

2.2. 두 번째 해커 hacker_id = 102의 submission_date = 2016-03-02 예시

• s1.submission_date = 2016-03-02일 때, s1.hacker_id = 102의 이전 날짜인 2016-03-01을 확인한다.

• s2는 hacker_id = 102이고 submission_date <= 2016-03-02인 모든 날짜를 찾는다.

  SELECT 1
  FROM Submissions s2
  WHERE s2.hacker_id = 102
  AND s2.submission_date <= '2016-03-02'
  GROUP BY s2.hacker_id
  HAVING COUNT(DISTINCT s2.submission_date) = DATEDIFF(day, '2016-03-01', '2016-03-02') + 1

• DATEDIFF(day, '2016-03-01', '2016-03-02') + 1은 2일로 계산된다. 이는 102 해커가 2016-03-01과 2016-03-02에 제출했음을 의미하며, 두 날짜는 연속된 2일의 기록이다.

• 그러나 102 해커는 2016-03-03에 제출하지 않았다. 따라서 연속된 3일의 기록을 찾을 수 없으므로 EXISTS 절은 거짓이 되어 해당 행은 결과에서 제외된다.

서브쿼리와 EXISTS 절의 역할

EXISTS 절은 행이 존재하는지 여부를 판단하는 데 사용된다. 서브쿼리는 각 s1.submission_date에 대해 반복적으로 실행되어, 그 날짜가 연속적인 날짜들을 포함하는지 여부를 검사한다.

• 서브쿼리에서 s2.submission_date <= s1.submission_date 조건이 적용되면, 현재 s1.submission_date에 대해 그 이전 날짜들을 찾게 된다.
• COUNT(DISTINCT s2.submission_date)는 해당 해커가 제출한 날짜들이 연속적인지 확인하는 역할을 한다.

예시로 확인하기

1. s1.submission_date = '2016-03-02'일 때:

   • 서브쿼리에서 s2.hacker_id = 102이고 s2.submission_date <= '2016-03-02'인 날짜들을 찾는다. 즉, 2016-03-01과 2016-03-02가 선택된다.
   • COUNT(DISTINCT s2.submission_date)는 2가 된다. 이 값은 2016-03-01과 2016-03-02가 연속적으로 존재함을 의미한다.

2. 연속된 날짜 여부 확인:

   • 서브쿼리에서 COUNT(DISTINCT s2.submission_date) = 2로 계산된 후, EXISTS 조건이 참이 되면 해당 날짜가 연속된 날짜임을 확인할 수 있다.
   • 2016-03-03에 대한 제출이 없으므로, 연속된 3일의 기록은 없으며 EXISTS 조건은 거짓이 되어 해당 날짜는 제외된다.

CTE_CONSECUTIVE에서의 사용

• 서브쿼리의 실행 결과가 EXISTS 조건을 참으로 만들면, CTE_CONSECUTIVE의 COUNT(DISTINCT s1.hacker_id)에서 그 날짜의 해커 수가 계산된다.
• 즉, 각 날짜에 대해 연속적인 날짜들이 있는지 확인한 후, 해당 날짜가 연속적인 기록을 가진 해커의 수를 계산하여 결과로 출력된다.

요약

1. 메인 쿼리에서 각 날짜(s1.submission_date)를 선택하고, 그 날짜에 대해 서브쿼리가 실행된다.
2. 서브쿼리는 s1.submission_date에 대해 해당 날짜가 연속적인지 확인하는 역할을 한다.
3. EXISTS 절은 서브쿼리의 결과에 따라 해당 날짜가 연속적인 날짜들을 포함하는지 판단한다.
4. EXISTS가 참이면 해당 날짜는 연속된 기록을 가진 해커로 카운트된다.