SQL Eligibility Criteria Query | Dataford Interview Questions

Given a database schema representing clinical trials and patients, write a query to find patients who meet specific eligibility criteria. Include only recruiting trials where the patient meets the trial's age range, has the required condition, and does not have the trial's exclusion condition.

Output

Return one row per eligible patient and trial with patient_id, patient_name, trial_id, and trial_name.
Sort by trial_id, then patient_id.

Schema

clinical_trials

Column	Type	Description
trial_idPK	INT	Unique clinical trial identifier
trial_name	VARCHAR(150)	Clinical trial name
enrollment_status	VARCHAR(30)	Current enrollment status
target_condition	VARCHAR(100)	Condition required for eligibility
excluded_condition	VARCHAR(100)	Condition that disqualifies a patient
min_age	INT	Minimum eligible age
max_age	INT	Maximum eligible age

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
patient_name	VARCHAR(120)	Patient display name
age_years	INT	Patient age in completed years

patient_conditions

Column	Type	Description
condition_record_idPK	INT	Unique condition record identifier
patient_id	INT	Referenced patient
condition_name	VARCHAR(100)	Patient condition

Your question is SQL Eligibility Criteria Query. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem