Find COVID Recovery Patients - Problem
COVID Recovery Patient Tracker
In the midst of the pandemic, hospitals need to track patient recovery patterns to understand treatment effectiveness and plan resources. You're tasked with creating a system to identify patients who have successfully recovered from COVID-19.
What defines recovery?
A patient is considered recovered if they:
• Had at least one
• Later had at least one
Your mission:
Find all recovered patients and calculate their recovery time - the number of days between their first positive test and their first negative test after that positive test.
Database Schema:
Return results ordered by recovery_time (ascending), then by patient_name (ascending).
In the midst of the pandemic, hospitals need to track patient recovery patterns to understand treatment effectiveness and plan resources. You're tasked with creating a system to identify patients who have successfully recovered from COVID-19.
What defines recovery?
A patient is considered recovered if they:
• Had at least one
Positive test result• Later had at least one
Negative test result on a subsequent dateYour mission:
Find all recovered patients and calculate their recovery time - the number of days between their first positive test and their first negative test after that positive test.
Database Schema:
patients table: Contains patient information (patient_id, patient_name, age)covid_tests table: Contains test results (test_id, patient_id, test_date, result)Return results ordered by recovery_time (ascending), then by patient_name (ascending).
Input & Output
example_1.py — Basic Recovery Case
$
Input:
patients: [(1, 'Alice', 30), (2, 'Bob', 25)]
covid_tests: [(1, 1, '2023-01-01', 'Negative'), (2, 1, '2023-01-05', 'Positive'), (3, 1, '2023-01-15', 'Negative'), (4, 2, '2023-01-10', 'Positive')]
›
Output:
[(1, 'Alice', 10)]
💡 Note:
Alice tested positive on 2023-01-05 and negative on 2023-01-15, taking 10 days to recover. Bob only has positive tests, so he's not included.
example_2.py — Multiple Patients
$
Input:
patients: [(1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)]
covid_tests: [(1, 1, '2023-01-05', 'Positive'), (2, 1, '2023-01-15', 'Negative'), (3, 2, '2023-01-01', 'Positive'), (4, 2, '2023-01-08', 'Negative'), (5, 3, '2023-01-03', 'Positive'), (6, 3, '2023-01-20', 'Negative')]
›
Output:
[(2, 'Bob', 7), (1, 'Alice', 10), (3, 'Charlie', 17)]
💡 Note:
Bob recovered in 7 days, Alice in 10 days, Charlie in 17 days. Results are ordered by recovery time ascending.
example_3.py — Edge Case with Same Names
$
Input:
patients: [(1, 'Alice', 30), (2, 'Alice', 25)]
covid_tests: [(1, 1, '2023-01-05', 'Positive'), (2, 1, '2023-01-20', 'Negative'), (3, 2, '2023-01-01', 'Positive'), (4, 2, '2023-01-16', 'Negative')]
›
Output:
[(1, 'Alice', 15), (2, 'Alice', 15)]
💡 Note:
Both patients named Alice recovered in 15 days. When recovery times are equal, results maintain original order or can be ordered by patient_id.
Constraints
- 1 ≤ patient_id ≤ 104
- 1 ≤ test_id ≤ 105
- patient_name consists of lowercase English letters and spaces
- result is one of: 'Positive', 'Negative', 'Inconclusive'
- Only patients with both positive and negative results are included
- test_date is in YYYY-MM-DD format
Visualization
Tap to expand
Understanding the Visualization
1
Timeline Setup
Each patient has a chronological sequence of test results
2
First Positive
Identify when the patient first tested positive (illness start)
3
First Negative After
Find the first negative test that occurs after any positive test
4
Recovery Calculation
Calculate days between first positive and first subsequent negative
Key Takeaway
🎯 Key Insight: Use window functions to efficiently process patient timelines and conditional aggregation to find the critical recovery dates in a single database pass.
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code