Patients With a Condition

Difficulty: Easy | Acceptance: 38.90% | Paid: No Topics: Database

Table: Patients +--------------+---------+ | Column Name | Type | +--------------+---------+ | patient_id | int | | patient_name | varchar | | conditions | varchar | +--------------+---------+ patient_id is the primary key for this table. ‘conditions’ contains 0 or more code(s) separated by spaces. For example, the code “DIAB100” is a code for Type I Diabetes, while “DIAB200” is a code for Type II Diabetes.

Write an SQL query to report the patient_id, patient_name, and conditions of the patients who have Type I Diabetes. Type I Diabetes has a code that always starts with “DIAB1”. It can be in any position in the conditions list.

The result format is in the following example.

• Examples
• Constraints
• Approach 1: Using LIKE Operator
• Approach 2: Using REGEXP
• Approach 3: Using String Position Functions

Examples

Example 1:

Input:
Patients table:
+------------+--------------+--------------+
| patient_id | patient_name | conditions   |
+------------+--------------+--------------+
| 1          | Daniel       | DIAB100 MYOP |
| 2          | Alice        |              |
| 3          | Bob          | DIAB100      |
| 4          | George       | ACNE DIAB100 |
| 5          | Alain        | DIAB201      |
+------------+--------------+--------------+

Output:
+------------+--------------+--------------+
| patient_id | patient_name | conditions   |
+------------+--------------+--------------+
| 1          | Daniel       | DIAB100 MYOP |
| 3          | Bob          | DIAB100      |
| 4          | George       | ACNE DIAB100 |
+------------+--------------+--------------+

Explanation: Daniel, Bob, and George have Type I Diabetes because their conditions contain "DIAB1" (DIAB100).

Constraints

1 <= patient_id <= 1000
patient_name consists of lowercase and uppercase English letters.
conditions consists of uppercase English letters, spaces, and digits.
0 <= conditions.length <= 100

Approach 1: Using LIKE Operator

Intuition Use pattern matching with the LIKE operator to find rows where the conditions column contains “DIAB1” anywhere in the string.

Steps

• Use SELECT * to get all columns
• Use WHERE conditions LIKE ‘%DIAB1%’ to match any string containing “DIAB1”

import pandas as pd

def find_patients(patients: pd.DataFrame) -> pd.DataFrame:
    return patients[patients['conditions'].str.contains('DIAB1', na=False)]

Complexity

• Time: O(n) where n is the number of rows
• Space: O(1) for the query, O(k) for result storage where k is the number of matching rows
• Notes: Simple and readable, but may be slower for large datasets

Approach 2: Using REGEXP

Intuition Use regular expressions to match the pattern “DIAB1” in the conditions column.

Steps

• Use SELECT * to get all columns
• Use WHERE conditions REGEXP ‘DIAB1’ to match using regular expression

import pandas as pd
import re

def find_patients(patients: pd.DataFrame) -> pd.DataFrame:
    pattern = re.compile(r'DIAB1')
    return patients[patients['conditions'].str.contains(pattern, na=False)]

Complexity

• Time: O(n) where n is the number of rows
• Space: O(1) for the query, O(k) for result storage where k is the number of matching rows
• Notes: More flexible for complex patterns, but may have overhead for simple patterns

Approach 3: Using String Position Functions

Intuition Use string position functions to check if “DIAB1” exists in the conditions column.

Steps

• Use SELECT * to get all columns
• Use WHERE LOCATE(‘DIAB1’, conditions) > 0 or similar position function

import pandas as pd

def find_patients(patients: pd.DataFrame) -> pd.DataFrame:
    return patients[patients['conditions'].str.find('DIAB1') >= 0]

Complexity

• Time: O(n) where n is the number of rows
• Space: O(1) for the query, O(k) for result storage where k is the number of matching rows
• Notes: Similar to LIKE, but may be more efficient in some database engines