Patient Metrics with Window Functions | Dataford Interview Questions

Given a highly normalized database schema, write a SQL query to extract a specific set of patient metrics, utilizing window functions.

Use the normalized patient, facility, and encounter data. Include all patients and calculate metrics for encounters beginning in calendar year 2025.

Output

One row per patient, including patients with no qualifying encounters.
Columns: patient_id, patient_name, facility_id, encounter_count, average_length_hours, latest_encounter_date, and facility_encounter_rank.
Rank patients within each facility by encounter count descending, with ties sharing a rank.
Order by facility, rank, and patient ID, placing patients without a facility last.

facilities

Column	Type	Description
facility_idPK	INT	Unique facility identifier
facility_name	VARCHAR(100)	Facility display name

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
first_name	VARCHAR(50)	Patient first name
last_name	VARCHAR(50)	Patient last name
facility_id	INT	Assigned facility identifier

encounters

Column	Type	Description
encounter_idPK	INT	Unique encounter identifier
patient_id	INT	Patient associated with the encounter
facility_id	INT	Facility where the encounter occurred
admit_ts	TIMESTAMP	Encounter start timestamp
discharge_ts	TIMESTAMP	Encounter discharge timestamp

Your question is Patient Metrics with Window Functions. 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.