Tables
erDiagram
patients {
patients_id int PK
patients_name varchar
age int
}
covid_tests {
test_id int PK
patients_id int FK
test_date date
result varchar "Positive, Negative, Inconclusive"
}
patients || -- o{ covid_tests : "Test result"
Mermaid
복사
Question
Write a solution to find patients who have recovered from COVID - patients who tested positive but later tested negative.
•
A patient is considered recovered if they have at least one Positive test followed by at least one Negative test on a later date
•
Calculate the recovery time in days as the difference between the first positive test and the first negative test after that positive test
•
Only include patients who have both positive and negative test results
Return the result table ordered by recovery_time in ascending order, then by patient_name in ascending order.
The result format is in the following example.
DataFlow
groupby patient_id -> case when positive -> min test_date -> p-date
join p-date < test_date -> groupby patient_id -> case when negative -> min test_date -> n-date
Answer
WITH result_p AS (
SELECT
patient_id,
MIN(CASE WHEN result = "Positive" THEN test_date END) AS p_date
FROM covid_tests
GROUP BY patient_id
),
result_n AS (
SELECT
c.patient_id,
MIN(CASE WHEN c.result = "Negative" THEN c.test_date END) AS n_date
FROM covid_tests c
INNER JOIN result_p p
ON c.patient_id = p.patient_id
AND c.test_date > p.p_date
GROUP BY patient_id
)
SELECT
p.patient_id,
info.patient_name,
info.age,
DATEDIFF(n.n_date, p.p_date) AS recovery_time
FROM result_p p
INNER JOIN result_n n
ON p.patient_id = n.patient_id
AND p.p_date IS NOT NULL
AND n.n_date IS NOT NULL
INNER JOIN patients info
ON p.patient_id = info.patient_id
ORDER BY DATEDIFF(n.n_date, p.p_date) ASC, info.patient_name ASC;
SQL
복사
case 문을 where로 빼는 방법도 있다.
WITH result_p AS (
SELECT
patient_id,
MIN(test_date) AS p_date
FROM covid_tests
WHERE result = "Positive"
GROUP BY patient_id
),
result_n AS (
SELECT
c.patient_id,
MIN(c.test_date) AS n_date
FROM covid_tests c
INNER JOIN result_p p
ON c.patient_id = p.patient_id
AND result = "Negative"
AND c.test_date > p.p_date
GROUP BY patient_id
)
SELECT
p.patient_id,
info.patient_name,
info.age,
DATEDIFF(n.n_date, p.p_date) AS recovery_time
FROM result_p p
INNER JOIN result_n n
ON p.patient_id = n.patient_id
INNER JOIN patients info
ON p.patient_id = info.patient_id
ORDER BY DATEDIFF(n.n_date, p.p_date) ASC, info.patient_name ASC;
SQL
복사