Patients With a Condition | LeetCode | MSSQL

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).

SELECT *
FROM Patients
WHERE conditions LIKE 'DIAB1%'
  OR conditions LIKE '% DIAB1%'

This query has a runtime of 677ms, beating 20.93% of submissions.

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.

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.

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.

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.

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.

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.

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.

For more insightful solutions and tech-related content, feel free to connect with me on my Beacons page.

ranggakd - Link in Bio & Creator Tools | Beacons

@ranggakd | center details summary summary Oh hello there I m a an Programmer AI Tech Writer Data Practitioner Statistics Math Addict Open Source Contributor Quantum Computing Enthusiast details center.

beacons.ai