SQL for Patient Data Extraction

MediumSQL · PostgreSQL

Problem

Grady Health System needs a patient-level summary of active patients who had multiple completed visits during calendar year 2025. Write a PostgreSQL query using the provided tables.

Requirements

Include only patients whose status is Active and whose visits were completed between January 1 and December 31, 2025.
Return the patient identifier, MRN, full name, completed visit count, total charges, latest visit date, and department for the latest visit.
Include only patients with at least two qualifying visits, and sort by total charges descending, then patient ID ascending.

Use a CTE with ROW_NUMBER() to identify each patient's latest qualifying visit. A patient whose latest visit has no department should still appear with a NULL department.

Schema

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
mrn	VARCHAR(20)	Medical record number
first_name	VARCHAR(50)	Patient first name
last_name	VARCHAR(50)	Patient last name
date_of_birth	DATE	Patient date of birth
sex	VARCHAR(20)	Recorded sex
status	VARCHAR(20)	Patient status

visits

Column	Type	Description
visit_idPK	INT	Unique visit identifier
patient_id	INT	Referenced patient
department_id	INT	Referenced department
visit_date	DATE	Date of service
visit_type	VARCHAR(30)	Visit type
visit_status	VARCHAR(20)	Visit completion status
charge	NUMERIC(10,2)	Billed charge

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Grady Health System department name

Tablespatientsvisitsdepartments