SQL Patient Data Aggregation | Dataford Interview Questions

Write a SQL query to retrieve and aggregate patient data from multiple relational tables.

Return active patients and summarize qualifying completed visits occurring during calendar year 2025. Include patients without qualifying visits, and treat missing aggregate values as zero where appropriate.

Output

One row per active patient with the patient ID and name.
Include visit count, total charges, distinct diagnosis count, and latest qualifying visit date.
Order by total charges descending, then patient ID ascending.

Schema

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
patient_name	VARCHAR(100)	Patient full name
status	VARCHAR(20)	Patient account status

visits

Column	Type	Description
visit_idPK	INT	Unique visit identifier
patient_id	INT	Referenced patient
visit_date	DATE	Date of the visit
visit_status	VARCHAR(20)	Visit completion status
charge	DECIMAL(10,2)	Amount charged for the visit

diagnoses

Column	Type	Description
diagnosis_idPK	INT	Unique diagnosis record identifier
visit_id	INT	Referenced visit
diagnosis_code	VARCHAR(20)	Clinical diagnosis code

Your question is SQL Patient Data Aggregation. 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