Adherence Rates Query | Dataford Interview Questions

Write a SQL query to identify patient adherence rates over a twelve-month treatment cycle.

Use the provided patient, treatment cycle, and dose record data. Treat each cycle as twelve expected monthly treatment periods. A month is completed when at least one dose record for that month has status taken; missing or non-taken records do not count.

Output

One row per treatment cycle.
Columns: patient_id, patient_name, cycle_start, months_expected, months_completed, and adherence_rate_pct.
Include cycles with no completed months, order by adherence rate descending, then patient_id ascending.

Schema

patients

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

treatment_cycles

Column	Type	Description
cycle_idPK	INT	Unique treatment cycle identifier
patient_id	INT	Patient receiving the treatment cycle
cycle_start	DATE	Start date of the twelve-month cycle
cycle_end	DATE	Recorded end date of the cycle

dose_records

Column	Type	Description
dose_idPK	INT	Unique dose record identifier
patient_id	INT	Patient associated with the dose
scheduled_date	DATE	Scheduled date of the dose
status	VARCHAR(20)	Dose status, such as taken, missed, or pending

Your question is Adherence Rates 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