Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit0c9d017

Browse files
authored
Added task 3586
1 parente82f6ea commit0c9d017

File tree

3 files changed

+244
-0
lines changed

3 files changed

+244
-0
lines changed
Lines changed: 105 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,105 @@
1+
3586\. Find COVID Recovery Patients
2+
3+
Medium
4+
5+
Table:`patients`
6+
7+
+-------------+---------+
8+
| Column Name | Type |
9+
+-------------+---------+
10+
| patient_id | int |
11+
| patient_name| varchar |
12+
| age | int |
13+
+-------------+---------+
14+
patient_id is the unique identifier for this table.
15+
Each row contains information about a patient.
16+
17+
Table:`covid_tests`
18+
19+
+-------------+---------+
20+
| Column Name | Type |
21+
+-------------+---------+
22+
| test_id | int |
23+
| patient_id | int |
24+
| test_date | date |
25+
| result | varchar |
26+
+-------------+---------+
27+
test_id is the unique identifier for this table.
28+
Each row represents a COVID test result. The result can be Positive, Negative, or Inconclusive.
29+
30+
Write a solution to find patients who have**recovered from COVID** - patients who tested positive but later tested negative.
31+
32+
* A patient is considered recovered if they have**at least one****Positive** test followed by at least one**Negative** test on a**later date**
33+
* Calculate the**recovery time** in days as the**difference** between the**first positive test** and the**first negative test** after that**positive test**
34+
***Only include** patients who have both positive and negative test results
35+
36+
Return_the result table ordered by_`recovery_time`_in**ascending** order, then by_`patient_name`_in**ascending** order_.
37+
38+
The result format is in the following example.
39+
40+
**Example:**
41+
42+
**Input:**
43+
44+
patients table:
45+
46+
+------------+--------------+-----+
47+
| patient_id | patient_name | age |
48+
+------------+--------------+-----+
49+
| 1 | Alice Smith | 28 |
50+
| 2 | Bob Johnson | 35 |
51+
| 3 | Carol Davis | 42 |
52+
| 4 | David Wilson | 31 |
53+
| 5 | Emma Brown | 29 |
54+
+------------+--------------+-----+
55+
56+
covid\_tests table:
57+
58+
+---------+------------+------------+--------------+
59+
| test_id | patient_id | test_date | result |
60+
|---------|------------|------------|--------------|
61+
| 1 | 1 | 2023-01-15 | Positive |
62+
| 2 | 1 | 2023-01-25 | Negative |
63+
| 3 | 2 | 2023-02-01 | Positive |
64+
| 4 | 2 | 2023-02-05 | Inconclusive |
65+
| 5 | 2 | 2023-02-12 | Negative |
66+
| 6 | 3 | 2023-01-20 | Negative |
67+
| 7 | 3 | 2023-02-10 | Positive |
68+
| 8 | 3 | 2023-02-20 | Negative |
69+
| 9 | 4 | 2023-01-10 | Positive |
70+
| 10 | 4 | 2023-01-18 | Positive |
71+
| 11 | 5 | 2023-02-15 | Negative |
72+
| 12 | 5 | 2023-02-20 | Negative |
73+
+---------+------------+------------+--------------+
74+
75+
**Output:**
76+
77+
+------------+--------------+-----+---------------+
78+
| patient_id | patient_name | age | recovery_time |
79+
|------------|--------------|-----|---------------|
80+
| 1 | Alice Smith | 28 | 10 |
81+
| 3 | Carol Davis | 42 | 10 |
82+
| 2 | Bob Johnson | 35 | 11 |
83+
+------------+--------------+-----+---------------+
84+
85+
**Explanation:**
86+
87+
***Alice Smith (patient\_id = 1):**
88+
* First positive test: 2023-01-15
89+
* First negative test after positive: 2023-01-25
90+
* Recovery time: 25 - 15 = 10 days
91+
***Bob Johnson (patient\_id = 2):**
92+
* First positive test: 2023-02-01
93+
* Inconclusive test on 2023-02-05 (ignored for recovery calculation)
94+
* First negative test after positive: 2023-02-12
95+
* Recovery time: 12 - 1 = 11 days
96+
***Carol Davis (patient\_id = 3):**
97+
* Had negative test on 2023-01-20 (before positive test)
98+
* First positive test: 2023-02-10
99+
* First negative test after positive: 2023-02-20
100+
* Recovery time: 20 - 10 = 10 days
101+
***Patients not included:**
102+
* David Wilson (patient\_id = 4): Only has positive tests, no negative test after positive
103+
* Emma Brown (patient\_id = 5): Only has negative tests, never tested positive
104+
105+
Output table is ordered by recovery\_time in ascending order, and then by patient\_name in ascending order.
Lines changed: 59 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,59 @@
1+
# Write your MySQL query statement below
2+
# #Medium #Database #2025_06_19_Time_471_ms_(97.17%)_Space_0.0_MB_(100.00%)
3+
-- mysql
4+
-- SELECT
5+
-- p.patient_id,
6+
-- p.patient_name,
7+
-- p.age,
8+
-- DATEDIFF(
9+
-- min(neg.test_date),
10+
-- min(pos.test_date)
11+
-- ) AS recovery_time
12+
-- FROM
13+
-- patients p
14+
-- JOIN covid_tests pos ON
15+
-- p.patient_id = pos.patient_id AND pos.result = 'Positive'
16+
-- JOIN covid_tests neg ON
17+
-- p.patient_id = neg.patient_id AND neg.result = 'Negative'
18+
-- WHERE
19+
-- neg.test_date > pos.test_date
20+
-- GROUP BY
21+
-- p.patient_id, p.patient_name, p.age
22+
-- ORDER BY
23+
-- recovery_time, p.patient_name;
24+
select
25+
p.patient_id,
26+
p.patient_name,
27+
p.age,
28+
datediff(
29+
day,
30+
pos.first_pos_date,
31+
neg.first_neg_date
32+
)as recovery_time
33+
from
34+
patients p
35+
join (
36+
select patient_id,min(test_date)as first_pos_date
37+
from covid_tests
38+
where result='Positive'
39+
group by patient_id
40+
) posonp.patient_id=pos.patient_id
41+
join (
42+
select
43+
c1.patient_id,
44+
min(c1.test_date)as first_neg_date
45+
from
46+
covid_tests c1
47+
join (
48+
select patient_id,min(test_date)as first_pos_date
49+
from covid_tests
50+
where result='Positive'
51+
group by patient_id
52+
) p2onc1.patient_id=p2.patient_id
53+
where
54+
c1.result='Negative'
55+
andc1.test_date>p2.first_pos_date
56+
group byc1.patient_id
57+
) negonp.patient_id=neg.patient_id
58+
order by
59+
recovery_timeASC,p.patient_nameASC;
Lines changed: 80 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,80 @@
1+
packageg3501_3600.s3586_find_covid_recovery_patients;
2+
3+
importstaticorg.hamcrest.CoreMatchers.equalTo;
4+
importstaticorg.hamcrest.MatcherAssert.assertThat;
5+
6+
importjava.io.BufferedReader;
7+
importjava.io.FileNotFoundException;
8+
importjava.io.FileReader;
9+
importjava.sql.Connection;
10+
importjava.sql.ResultSet;
11+
importjava.sql.SQLException;
12+
importjava.sql.Statement;
13+
importjava.util.stream.Collectors;
14+
importjavax.sql.DataSource;
15+
importorg.junit.jupiter.api.Test;
16+
importorg.zapodot.junit.db.annotations.EmbeddedDatabase;
17+
importorg.zapodot.junit.db.annotations.EmbeddedDatabaseTest;
18+
importorg.zapodot.junit.db.common.CompatibilityMode;
19+
20+
@EmbeddedDatabaseTest(
21+
compatibilityMode =CompatibilityMode.MySQL,
22+
initialSqls =
23+
"CREATE TABLE patients (patient_id INTEGER, patient_name VARCHAR(255), age INTEGER); "
24+
+"INSERT INTO patients (patient_id, patient_name, age) VALUES"
25+
+"(1, 'Alice Smith', 28),"
26+
+"(2, 'Bob Johnson', 35),"
27+
+"(3, 'Carol Davis', 42),"
28+
+"(4, 'David Wilson', 31),"
29+
+"(5, 'Emma Brown', 29);"
30+
+"CREATE TABLE covid_tests (test_id INTEGER, patient_id INTEGER"
31+
+", test_date DATE, result VARCHAR(255)); "
32+
+"INSERT INTO covid_tests (test_id, patient_id, test_date, result) VALUES"
33+
+"(1, 1, '2023-01-15', 'Positive'),"
34+
+"(2, 1, '2023-01-25', 'Negative'),"
35+
+"(3, 2, '2023-02-01', 'Positive'),"
36+
+"(4, 2, '2023-02-05', 'Inconclusive'),"
37+
+"(5, 2, '2023-02-12', 'Negative'),"
38+
+"(6, 3, '2023-01-20', 'Negative'),"
39+
+"(7, 3, '2023-02-10', 'Positive'),"
40+
+"(8, 3, '2023-02-20', 'Negative'),"
41+
+"(9, 4, '2023-01-10', 'Positive'),"
42+
+"(10, 4, '2023-01-18', 'Positive'),"
43+
+"(11, 5, '2023-02-15', 'Negative'),"
44+
+"(12, 5, '2023-02-20', 'Negative');")
45+
classMysqlTest {
46+
@Test
47+
voidtestScript(@EmbeddedDatabaseDataSourcedataSource)
48+
throwsSQLException,FileNotFoundException {
49+
try (finalConnectionconnection =dataSource.getConnection()) {
50+
try (finalStatementstatement =connection.createStatement();
51+
finalResultSetresultSet =
52+
statement.executeQuery(
53+
newBufferedReader(
54+
newFileReader(
55+
"src/main/java/g3501_3600/"
56+
+"s3586_find_covid_recovery_patients/"
57+
+"script.sql"))
58+
.lines()
59+
.collect(Collectors.joining("\n"))
60+
.replaceAll("#.*?\\r?\\n",""))) {
61+
assertThat(resultSet.next(),equalTo(true));
62+
assertThat(resultSet.getNString(1),equalTo("1"));
63+
assertThat(resultSet.getNString(2),equalTo("Alice Smith"));
64+
assertThat(resultSet.getNString(3),equalTo("28"));
65+
assertThat(resultSet.getNString(4),equalTo("10"));
66+
assertThat(resultSet.next(),equalTo(true));
67+
assertThat(resultSet.getNString(1),equalTo("3"));
68+
assertThat(resultSet.getNString(2),equalTo("Carol Davis"));
69+
assertThat(resultSet.getNString(3),equalTo("42"));
70+
assertThat(resultSet.getNString(4),equalTo("10"));
71+
assertThat(resultSet.next(),equalTo(true));
72+
assertThat(resultSet.getNString(1),equalTo("2"));
73+
assertThat(resultSet.getNString(2),equalTo("Bob Johnson"));
74+
assertThat(resultSet.getNString(3),equalTo("35"));
75+
assertThat(resultSet.getNString(4),equalTo("11"));
76+
assertThat(resultSet.next(),equalTo(false));
77+
}
78+
}
79+
}
80+
}

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp