Find COVID Recovery Patients - Problem

Given two tables patients and covid_tests, find patients who have recovered from COVID.

Recovery Definition: A patient is considered recovered if they have at least one Positive test followed by at least one Negative test on a later date.

Requirements:

  • 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 results ordered by recovery_time ascending, then by patient_name ascending

Table Schema

patients
Column Name Type Description
patient_id PK int Unique identifier for each patient
patient_name varchar Name of the patient
age int Age of the patient
Primary Key: patient_id
covid_tests
Column Name Type Description
test_id PK int Unique identifier for each test
patient_id int Foreign key referencing patients table
test_date date Date when the test was conducted
result varchar Test result: Positive, Negative, or Inconclusive
Primary Key: test_id

Input & Output

Example 1 — Basic Recovery Case
Input Tables:
patients
patient_id patient_name age
1 Alice 30
2 Bob 25
3 Charlie 35
covid_tests
test_id patient_id test_date result
1 1 2024-01-01 Positive
2 1 2024-01-10 Negative
3 2 2024-01-05 Positive
4 2 2024-01-12 Negative
5 3 2024-01-03 Negative
Output:
patient_name first_positive_date first_negative_date recovery_time
Bob 2024-01-05 2024-01-12 7
Alice 2024-01-01 2024-01-10 9
💡 Note:

Alice tested positive on 2024-01-01 and negative on 2024-01-10 (9 days recovery). Bob tested positive on 2024-01-05 and negative on 2024-01-12 (7 days recovery). Charlie only has negative tests, so is not included. Results are ordered by recovery_time (7, 9) then by patient_name.
Example 2 — Multiple Tests Scenario
Input Tables:
patients
patient_id patient_name age
1 David 40
2 Eve 28
covid_tests
test_id patient_id test_date result
1 1 2024-01-01 Positive
2 1 2024-01-05 Positive
3 1 2024-01-10 Negative
4 1 2024-01-15 Negative
5 2 2024-01-02 Positive
Output:
patient_name first_positive_date first_negative_date recovery_time
David 2024-01-01 2024-01-10 9
💡 Note:

David has multiple positive and negative tests. We take his first positive (2024-01-01) and first negative after that (2024-01-10) for 9 days recovery. Eve only has positive tests with no negative follow-up, so she's not included in the results.
Example 3 — Edge Case: No Recovery
Input Tables:
patients
patient_id patient_name age
1 Frank 45
covid_tests
test_id patient_id test_date result
1 1 2024-01-01 Negative
2 1 2024-01-05 Positive
3 1 2024-01-03 Negative
Output:
patient_name first_positive_date first_negative_date recovery_time
💡 Note:

Frank has a negative test on 2024-01-03 that comes before his positive test on 2024-01-05, but no negative test after the positive. Since there's no negative test after the positive test, Frank is not considered recovered and doesn't appear in the results.

Constraints

  • 1 ≤ patient_id ≤ 1000
  • 1 ≤ test_id ≤ 10000
  • result is one of 'Positive', 'Negative', or 'Inconclusive'
  • test_date is a valid date
  • patient_name contains only letters and spaces

Visualization

Tap to expand
Find COVID Recovery Patients INPUT TABLES patients id name 1 Alice 2 Bob 3 Charlie 4 Diana covid_tests pid date result 1 2021-01-01 Positive 1 2021-01-15 Negative 2 2021-02-01 Positive 2 2021-02-05 Negative 3 2021-03-01 Positive 3 2021-03-20 Negative 4 2021-04-01 Positive 4 2021-04-03 Positive (Diana: no negative) ALGORITHM STEPS 1 Find First Positive Test Get MIN(date) where result = 'Positive' per patient 2 Find First Negative After Get MIN(date) where result = 'Negative' AND date > pos_date 3 Calculate Recovery Time DATEDIFF(neg_date, pos_date) for each recovered patient 4 Order Results ORDER BY recovery_time, then patient_name SELECT p.name, DATEDIFF( MIN(n.date),MIN(pos.date)) AS recovery_time FROM patients p JOIN ... ORDER BY recovery_time, name FINAL RESULT Recovered Patients: name days Bob 4 Alice 14 Charlie 19 Recovery Timeline: Bob: 4 days Alice: 14 days Charlie: 19 days Diana - NOT Recovered Has Positive tests only No Negative test found Key Insight: Use self-joins with CTEs to find the first Positive test and first subsequent Negative test per patient. Recovery requires: 1) At least one Positive test, 2) At least one Negative test AFTER the positive date. DATEDIFF calculates days between dates. NULL handling ensures only true recoveries are counted. TutorialsPoint - Find COVID Recovery Patients | Optimal Solution

Related Problems

Asked in
Microsoft 15 Amazon 12 Google 8
23.4K Views
Medium Frequency
~18 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen