find-covid-recovery-patients

java

python3

python

javascript

typescript

csharp

golang

kotlin

swift

rust

ruby

dart

scala

elixir

erlang

racket

cangjie

bash

mysql

mssql

postgresql

oraclesql

pythondata

Database

Table: <code>patients</code>

<pre>
+-------------+---------+
| 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.
</pre>

Table: <code>covid_tests</code>

<pre>
+-------------+---------+
| 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.
</pre>

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

<ul>
	<li>A patient is considered recovered if they have at least one Positive test followed by at least one Negative test on a later date</li>
	<li>Calculate the recovery time in days as the difference between the first positive test and the first negative test after that positive test</li>
	<li>Only include patients who have both positive and negative test results</li>
</ul>

Return the result table ordered by <code>recovery_time</code> in ascending order, then by <code>patient_name</code> in ascending order.

The result format is in the following example.

&nbsp;
Example:

<div class="example-block">
Input:

patients table:

<pre class="example-io">
+------------+--------------+-----+
| 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 |
+------------+--------------+-----+
</pre>

covid_tests table:

<pre class="example-io">
+---------+------------+------------+--------------+
| 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 |
+---------+------------+------------+--------------+
</pre>

Output:

<pre class="example-io">
+------------+--------------+-----+---------------+
| patient_id | patient_name | age | recovery_time |
+------------+--------------+-----+---------------+
| 1 | Alice Smith | 28 | 10 |
| 3 | Carol Davis | 42 | 10 |
| 2 | Bob Johnson | 35 | 11 |
+------------+--------------+-----+---------------+
</pre>

Explanation:

<ul>
	<li>Alice Smith (patient_id = 1):

	<ul>
		<li>First positive test: 2023-01-15</li>
		<li>First negative test after positive: 2023-01-25</li>
		<li>Recovery time: 25 - 15 = 10 days</li>
	</ul>
	</li>
	<li>Bob Johnson (patient_id = 2):
	<ul>
		<li>First positive test: 2023-02-01</li>
		<li>Inconclusive test on 2023-02-05 (ignored for recovery calculation)</li>
		<li>First negative test after positive: 2023-02-12</li>
		<li>Recovery time: 12 - 1 = 11 days</li>
	</ul>
	</li>
	<li>Carol Davis (patient_id = 3):
	<ul>
		<li>Had negative test on 2023-01-20 (before positive test)</li>
		<li>First positive test: 2023-02-10</li>
		<li>First negative test after positive: 2023-02-20</li>
		<li>Recovery time: 20 - 10 = 10 days</li>
	</ul>
	</li>
	<li>Patients not included:
	<ul>
		<li>David Wilson (patient_id = 4): Only has positive tests, no negative test after positive</li>
		<li>Emma Brown (patient_id = 5): Only has negative tests, never tested positive</li>
	</ul>
	</li>
</ul>

Output table is ordered by recovery_time in ascending order, and then by patient_name in ascending order.
</div>

find_covid_recovery_patients

Find COVID Recovery Patients

数据库

表：<code>patients</code>

<pre>
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| patient_id | int |
| patient_name| varchar |
| age | int |
+-------------+---------+
patient_id 是这张表的唯一主键。
每一行表示一个患者的信息。
</pre>

表：<code>covid_tests</code>

<pre>
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| test_id | int |
| patient_id | int |
| test_date | date |
| result | varchar |
+-------------+---------+
test_id 是这张表的唯一主键。
每一行代表一个 COVID 检测结果。结果可以是阳性、阴性或不确定。
</pre>

编写一个解决方案以找到从 COVID 中康复的患者——那些曾经检测呈阳性但后来检测呈阴性的患者。

<ul>
	<li>患者如果 至少有一次阳性 检测结果后，在&nbsp;之后的日期 至少有一次 阴性 检测结果，则被认为已康复。</li>
	<li>计算从 首次阳性检测 结果到 该阳性检测 后的 首次阴性检测结果 之间的 康复时间（以天为单位）</li>
	<li>仅包括&nbsp;同时具有阳性及阴性检测结果的患者</li>
</ul>

返回结果表以&nbsp;<code>recovery_time</code> 升序 排序，然后以&nbsp;<code>patient_name</code> 升序&nbsp;排序。

结果格式如下所示。

&nbsp;

示例：

<div class="example-block">
输入：

patients 表：

<pre class="example-io">
+------------+--------------+-----+
| 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 |
+------------+--------------+-----+
</pre>

covid_tests 表：

<pre class="example-io">
+---------+------------+------------+--------------+
| 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 |
+---------+------------+------------+--------------+
</pre>

输出：

<pre class="example-io">
+------------+--------------+-----+---------------+
| patient_id | patient_name | age | recovery_time |
+------------+--------------+-----+---------------+
| 1 | Alice Smith | 28 | 10 |
| 3 | Carol Davis | 42 | 10 |
| 2 | Bob Johnson | 35 | 11 |
+------------+--------------+-----+---------------+
</pre>

解释：

<ul>
	<li>Alice Smith (patient_id = 1):

	<ul>
		<li>首次阳性检测：2023-01-15</li>
		<li>阳性检测后的首次阴性检测：2023-01-25</li>
		<li>康复时间：25 - 15 = 10 天</li>
	</ul>
	</li>
	<li>Bob Johnson (patient_id = 2):
	<ul>
		<li>首次阳性检测：2023-02-01</li>
		<li>测试结果不明确：2023-02-05（忽略计算康复时间）</li>
		<li>阳性检测后的首次阴性检测：2023-02-12</li>
		<li>康复时间：12 - 1 = 11 天</li>
	</ul>
	</li>
	<li>Carol Davis (patient_id = 3):
	<ul>
		<li>检测呈阴性：2023-01-20（在阳性检测前）</li>
		<li>首次阳性检测：2023-02-10</li>
		<li>阳性检测后的首次阴性检测：2023-02-20</li>
		<li>康复时间：20 - 10 = 10 天</li>
	</ul>
	</li>
	<li>没有包含的患者：
	<ul>
		<li>David Wilson（patient_id = 4）：只有阳性检测，之后没有阴性检测。</li>
		<li>Emma Brown（patient_id = 5）：只有阴性检测，从未有阳性检测。</li>
	</ul>
	</li>
</ul>

输出表以 recovery_time 升序排序，然后以 patient_name 升序排序。
</div>