시계열 데이터의 선후 관계: COVID-19 회복 기간 분석
3586. Find COVID Recovery Patients
1. Problem
환자별 COVID-19 테스트 기록에서 '최초 양성 판정' 이후 **'첫 번째 음성 판정'**이 나오기까지 걸린 시간(Recovery Time)을 계산해야 한다. 단순히 최소/최대 날짜를 구하는 것이 아니라, 특정 사건(양성)이 발생한 이후의 시점만을 필터링하여 추적하는 것이 이 문제의 핵심이다.
2. Solution
양성 판정 시점을 먼저 확정 짓는 CTE와, 그 시점 이후의 음성 기록만 필터링하는 CTE를 분리하여 설계한다. 사건의 순서가 명확해야 하므로 JOIN 조건에 날짜 비교(>)를 포함하여 논리적 선후 관계를 정립한다.
3. Takeaway (핵심 통계 논리)
시계열 분석에서 특정 상태 변화 사이의 간격을 구할 때 발생할 수 있는 오류를 방지하기 위해 다음 원칙을 적용한다.
- 상태 기반 시점 고정: 단순히 MIN(test_date)를 사용하는 것은 위험하다. 환자가 양성 판정 이전에 받았던 과거의 음성 기록이 섞여 들어올 수 있기 때문이다. 반드시 선행 지표(양성)를 먼저 고정한 뒤 이를 기준으로 후행 지표(음성)를 탐색해야 한다.
- 비등가 조인(Non-equi Join)의 활용: ct.test_date > fp.first_positive_date와 같은 조건을 JOIN 문에 직접 삽입함으로써, 대량의 데이터 중 우리가 추적하고자 하는 '사건 이후'의 데이터만 효율적으로 골라낼 수 있다.
- 날짜 함수 적용: MySQL 기준 DATEDIFF(종료일, 시작일)를 통해 두 지표 사이의 실제 소요 기간을 산출한다.
4. 정답 및 오답 쿼리 비교
(1) 정답 쿼리: 순차적 추적 로직
### 정답 쿼리 ###
WITH first_positive AS (
SELECT patient_id, MIN(test_date) AS first_positive_date
FROM covid_tests
WHERE result = 'Positive'
GROUP BY patient_id
),
first_negative_after_positive AS (
SELECT ct.patient_id, MIN(ct.test_date) AS first_negative_date
FROM covid_tests ct
JOIN first_positive fp ON ct.patient_id = fp.patient_id AND ct.test_date > fp.first_positive_date
WHERE ct.result = 'Negative'
GROUP BY ct.patient_id
)
SELECT fp.patient_id, p.patient_name, p.age, DATEDIFF(fn.first_negative_date,fp.first_positive_date) AS recovery_time
FROM first_positive fp
JOIN first_negative_after_positive fn ON fp.patient_id = fn.patient_id
JOIN patients p ON fp.patient_id = p.patient_id
ORDER BY recovery_time ASC, patient_name ASC;
이 문제의 결정적인 인사이트는 데이터의 흐름을 '세로(Row)'에서 '가로(Column)'로 전환하는 사고이다. 일반적으로 초보자들은 데이터를 행 단위로 보며 필터링하는 데 집중하지만, 중급 이상의 쿼리 테스트에서는 서로 다른 시점의 데이터를 한 줄(Row)에 나란히 놓고 비교하는 능력을 측정한다. 이 관점에서 이번 풀이가 주는 실무적 인사이트를 정리한다.
1. 가로로 펼치기 (Self JOIN)의 위력
우리가 작성한 정답 쿼리는 first_positive라는 정보 옆에 first_negative라는 정보를 가로로 붙여넣는 과정이다.
- 세로 모드 (Before): "이 환자의 모든 검사 기록을 시간순으로 나열한다." (어떤 게 양성이고 음성인지 눈으로 찾아야 함)
- 가로 모드 (After): "환자 ID | 양성 날짜 | 음성 날짜" 순으로 한 줄에 요약한다. (두 날짜의 차이를 계산할 수 있는 상태가 됨)
2. 왜 JOIN이 인사이트인가?
DATEDIFF나 산술 연산은 반드시 **동일한 행(Row)**에 있는 컬럼들끼리만 가능하다.
- 오답 쿼리처럼 한 테이블 내에서 CASE WHEN으로 해결하려 하면, 데이터의 선후 관계를 엄밀하게 따지기 어렵다.
- 반면 JOIN을 통해 데이터를 가로로 펼치면, **"A 사건이 일어난 날짜"**와 **"B 사건이 일어난 날짜"**가 한 쌍(Pair)이 되어 연산 가능한 구조로 변한다.
3. 실무적 확장성
이 '가로로 펼치기' 전략은 쿼리 테스트에서 다음과 같은 고난도 문제에 동일하게 적용된다.
- 전환율 분석: "회원가입 날짜" 옆에 "첫 구매 날짜"를 가로로 붙이기
- 물류/공정 분석: "주문 접수 시점" 옆에 "배송 완료 시점"을 가로로 붙이기
- 이탈 분석: "마지막 접속일" 옆에 "현재 날짜"를 가로로 붙이기
(2) 나의 오답 쿼리: 단순 집계의 함정
### 오답 쿼리 ###
WITH covid_period AS (
SELECT patient_id,
MIN(CASE WHEN result = 'Negative' THEN test_date END) AS first_negative_date,
MIN(CASE WHEN result = 'Positive' THEN test_date END) AS first_positive_date
FROM covid_tests
WHERE result = 'Positive' OR result = 'Negative'
GROUP BY patient_id
HAVING COUNT(DISTINCT result) = 2)
)
SELECT c.patient_id, p.patient_name, p.age,
DATEDIFF(c.first_negative_date, c.first_positive_date) AS recovery_time
FROM covid_period c
JOIN patients p ON c.patient_id = p.patient_id
WHERE c.first_negative_date > c.first_positive_date
ORDER BY recovery_time ASC, p.patient_name ASC;[실패 원인] 이 로직은 환자의 전체 기록 중 가장 빠른 음성 날짜와 가장 빠른 양성 날짜를 찾는다. 만약 환자가 과거에 음성 판정을 받았다가 나중에 감염(양성)된 케이스라면, 감염 전의 음성 날짜가 계산에 사용되어 recovery_time이 음수가 나오거나 잘못된 데이터가 산출된다.
5. 쿼리 핵심 포인트 분석
(1) 비즈니스 흐름과 쿼리의 일치
쿼리 설계 시 실제 현상(양성 판정 → 격리 → 음성 판정)의 흐름과 쿼리의 실행 순서를 일치시켜야 한다. 오답 쿼리는 '결과값의 집합'에 집중한 반면, 정답 쿼리는 '사건의 선후 관계'에 집중했다.
(2) 데이터 정제 단계의 분리
양성 시점과 음성 시점을 각각의 CTE로 분리함으로써 가독성을 높이고, 각 단계에서 발생할 수 있는 예외(양성만 있는 경우, 음성만 있는 경우 등)를 명확하게 제어할 수 있다.
(3) 정렬 및 최종 출력
DATEDIFF를 통한 수치 계산 후, 문제에서 요구한 대로 recovery_time 오름차순과 이름 오름차순을 적용하여 최종 보고서 형태를 완성한다.
오늘의 핵심 요약:
- 모든 MIN 값이 정답은 아니다. 그 데이터가 위치한 '시간대'가 어디인지 먼저 질문해야 한다.
- 두 이벤트 사이의 간격이나 차이를 구해야 한다면, 일단 데이터를 JOIN하여 가로로 길게 늘어뜨려 한 줄에 위치시켜라. 이 사고방식만 장착해도 리트코드 Medium 난이도의 50% 이상은 해결 로직이 바로 보일 것이다.