Patients With a Condition | LeetCode | MSSQL
Tags: beginners, programming, leetcode, mssql
Published: 2023-06-28 17:29:00

## The Problem
We're given a table called `Patients`:

| Column Name  | Type    |
|--------------|---------|
| patient_id   | int     |
| patient_name | varchar |
| conditions   | varchar |

The `patient_id` is the primary key for this table. The `conditions` field contains zero or more codes separated by spaces. The task is to write an SQL query that identifies patients who have Type I Diabetes. Type I Diabetes is always indicated with a code that starts with the prefix "DIAB1". The result should include the patient_id, patient_name, and conditions of such patients and can be returned in any order.

For instance, given the following input:

| patient_id | patient_name | conditions   |
|------------|--------------|--------------|
| 1          | Daniel       | YFEV COUGH   |
| 2          | Alice        |              |
| 3          | Bob          | DIAB100 MYOP |
| 4          | George       | ACNE DIAB100 |
| 5          | Alain        | DIAB201      |

The expected output is:

| patient_id | patient_name | conditions   |
|------------|--------------|--------------|
| 3          | Bob          | DIAB100 MYOP |
| 4          | George       | ACNE DIAB100 | 

Explanation: Bob and George both have a condition that starts with DIAB1.

## The Solution
We've derived four different solutions for this problem. All four solutions use variations of SQL's pattern matching capabilities to identify the necessary records, but each one approaches it differently, leveraging different SQL functions and strategies. 

### Source Code 1
This query utilizes the `LIKE` operator to search for the substring "DIAB1" in the `conditions` column. It's checking for two scenarios: `conditions` starting with "DIAB1" or "DIAB1" appearing after a space (indicating it is the start of a new condition).

```sql
SELECT *
FROM Patients
WHERE conditions LIKE 'DIAB1%'
  OR conditions LIKE '% DIAB1%'
```
This query has a runtime of 677ms, beating 20.93% of submissions.
![s1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kxvl1f7rabb22mt9csiq.png)

### Source Code 2
This solution employs the `PATINDEX` function to find the starting position of the "DIAB1" substring in the `conditions` column. It checks the same scenarios as the first solution.

```sql
SELECT *
FROM Patients
WHERE PATINDEX('DIAB1%', conditions) != 0
  OR PATINDEX('% DIAB1%', conditions) != 0
```
This solution has a runtime of 799ms, beating 11.24% of submissions.
![s2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/aa8v9i0g5d8d0pyykoug.png)

### Source Code 3
This code is identical to Source Code 2, but it specifically selects the `patient_id`, `patient_name`, and `conditions` columns to return, instead of using `SELECT *`. This can improve performance, especially if the original table has many columns.

```sql
SELECT
  patient_id,
  patient_name,
  conditions
FROM Patients
WHERE PATINDEX('DIAB1%', conditions) != 0
  OR PATINDEX('% DIAB1%', conditions) != 0
```
This query has a runtime of 591ms, beating 35.53% of submissions.
![s3](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yq03y8o73emd4rrhcahz.png)

### Source Code 4
Source Code 4 is identical to Source Code 3, so it should provide the same results. Variance in runtime is likely due to fluctuations in server load or database state rather than differences in the queries themselves.

```sql
SELECT
  patient_id,
  patient_name,
  conditions
FROM Patients
WHERE PATINDEX('DIAB1%', conditions) != 0
  OR PATINDEX('% DIAB1%', conditions) != 0
```
This query has a runtime of 635ms, beating 26.87% of submissions.
![s4](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/16h0jfyefwwht5jefou9.png)

## Conclusion
Based on the performance of these solutions in LeetCode, the most efficient solution would be Source Code 3, followed by Source Code 4, Source Code 1, and finally Source Code 2. However, keep in mind that the efficiency of SQL operations can significantly differ based on the specifics of the database, including its size, structure, indexing, and the database management system itself. 

You can find the original problem at [LeetCode](https://leetcode.com/problems/patients-with-a-condition/description/).

For more insightful solutions and tech-related content, feel free to connect with me on my [Beacons page](https://beacons.ai/ranggakd).

{% embed https://beacons.ai/ranggakd %}