SQL for Patient Adherence Metrics | Dataford Interview Questions

Write a SQL query to calculate patient adherence metrics over a specific treatment timeline.

Use the treatment plan dates as the timeline. Count only dose events marked taken whose dates fall within the plan, and exclude events with other or NULL statuses from the taken count.

Output

One row per treatment plan, including plans with no matching dose events.
Columns: patient_id, patient_name, plan_id, start_date, end_date, expected_doses, taken_doses, missed_doses, and adherence_percentage.
Order by patient_id, then plan_id ascending.

Schema

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
patient_name	VARCHAR(100)	Patient display name

treatment_plans

Column	Type	Description
plan_idPK	INT	Unique treatment plan identifier
patient_id	INT	Patient receiving the treatment
start_date	DATE	First date in the treatment timeline
end_date	DATE	Last date in the treatment timeline

dose_events

Column	Type	Description
event_idPK	INT	Unique dose event identifier
plan_id	INT	Treatment plan associated with the event
patient_id	INT	Patient associated with the event
dose_date	DATE	Date of the dose event
status	VARCHAR(40)	Recorded dose status

Your question is SQL for Patient Adherence Metrics. 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