Find COVID Recovery Patients - Practice Coding Problems

Find COVID Recovery Patients - Problem

Database Medium

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

Understanding the Visualization

Input Tables

Patients and their COVID test results

CTE Processing

Find first positive, then first negative after positive

Recovery Results

Calculate days between tests and order results

Key Takeaway

🎯 Key Insight: Use CTEs to separate the logic of finding chronological test sequences, making complex temporal queries manageable

Asked in

M Microsoft 15 A Amazon 12 G Google 8

Use CTEs with GROUP BY and MIN() to find the first positive test and first subsequent negative test for each patient. Join the results to calculate recovery time as the date difference, filtering for patients who have both positive and negative tests in the correct chronological order.

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

Common Approaches

Approach	Time	Space	Notes
✓ CTE with Window Functions	O(n log n)	O(n)	Use CTEs to find first positive and first negative tests, then join them

CTE with Window Functions — Algorithm Steps

Step 1: Find first positive test date for each patient
Step 2: Find first negative test after positive for each patient
Step 3: Join results and calculate recovery time

Visualization

Tap to expand

Step-by-Step Walkthrough

First Positive CTE

Find earliest positive test per patient

First Negative CTE

Find earliest negative test after positive

Join & Calculate

Combine results and calculate recovery time

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Sorting for window functions and joins

⚡ Linearithmic

Space Complexity

O(n)

Temporary CTE storage

⚡ Linearithmic Space

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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

CTE with Window Functions — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler