LeetCode-in-Java

3586. Find COVID Recovery Patients

Medium

Table: patients

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| patient_id  | int     |
| patient_name| varchar |
| age         | int     |
+-------------+---------+
patient_id is the unique identifier for this table.
Each row contains information about a patient.

Table: covid_tests

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| test_id     | int     |
| patient_id  | int     |
| test_date   | date    |
| result      | varchar |
+-------------+---------+
test_id is the unique identifier for this table.
Each row represents a COVID test result. The result can be Positive, Negative, or Inconclusive. 

Write a solution to find patients who have recovered from COVID - patients who tested positive but later tested negative.

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.

Example:

Input:

patients table:

+------------+--------------+-----+
| patient_id | patient_name | age |
+------------+--------------+-----+
| 1          | Alice Smith  | 28  |
| 2          | Bob Johnson  | 35  |
| 3          | Carol Davis  | 42  |
| 4          | David Wilson | 31  |
| 5          | Emma Brown   | 29  |
+------------+--------------+-----+ 

covid_tests table:

+---------+------------+------------+--------------+
| test_id | patient_id | test_date  | result       |
|---------|------------|------------|--------------|
| 1       | 1          | 2023-01-15 | Positive     |
| 2       | 1          | 2023-01-25 | Negative     |
| 3       | 2          | 2023-02-01 | Positive     |
| 4       | 2          | 2023-02-05 | Inconclusive |
| 5       | 2          | 2023-02-12 | Negative     |
| 6       | 3          | 2023-01-20 | Negative     |
| 7       | 3          | 2023-02-10 | Positive     |
| 8       | 3          | 2023-02-20 | Negative     |
| 9       | 4          | 2023-01-10 | Positive     |
| 10      | 4          | 2023-01-18 | Positive     |
| 11      | 5          | 2023-02-15 | Negative     |
| 12      | 5          | 2023-02-20 | Negative     |
+---------+------------+------------+--------------+

Output:

+------------+--------------+-----+---------------+
| patient_id | patient_name | age | recovery_time |
|------------|--------------|-----|---------------|
| 1          | Alice Smith  | 28  | 10            |
| 3          | Carol Davis  | 42  | 10            |
| 2          | Bob Johnson  | 35  | 11            |
+------------+--------------+-----+---------------+

Explanation:

Output table is ordered by recovery_time in ascending order, and then by patient_name in ascending order.

Solution

# Write your MySQL query statement below
SELECT 
    p.patient_id,
    p.patient_name,
    p.age,
    DATEDIFF(
        min(neg.test_date),
        min(pos.test_date)
    ) AS recovery_time
FROM 
    patients p
    JOIN covid_tests pos ON
        p.patient_id = pos.patient_id AND pos.result = 'Positive'
    JOIN covid_tests neg ON 
        p.patient_id = neg.patient_id AND neg.result = 'Negative'
WHERE 
    neg.test_date > pos.test_date
GROUP BY 
    p.patient_id, p.patient_name, p.age
ORDER BY 
    recovery_time, p.patient_name;