Rolling 7-Day Average with Window Functions

MediumSQL · PostgreSQL

Problem

Medpace clinical data analysts need to monitor patient measurements across research sites. Write a PostgreSQL query that calculates each patient's rolling 7-calendar-day average and ranks patients within their site based on their latest available rolling average.

Requirements

Join the Medpace site, patient, and measurement data.
Calculate a rolling average for each patient, including the measurement date and the six preceding calendar days.
Rank patients within each site by their latest rolling average in descending order. Patients with NULL averages must rank last.
Return only patients with at least one measurement, while retaining every measurement row and repeating the patient's site rank on each row.
Order the output by site, patient, and measurement date.

Schema

research_sites

Column	Type	Description
site_idPK	INT	Research site identifier
site_name	VARCHAR(100)	Medpace research site name

patients

Column	Type	Description
patient_idPK	INT	Patient identifier
site_id	INT	Research site assigned to the patient
patient_name	VARCHAR(100)	Patient display name

patient_measurements

Column	Type	Description
measurement_idPK	INT	Measurement identifier
patient_id	INT	Patient associated with the measurement
measurement_date	DATE	Date on which the measurement was recorded
measurement_value	NUMERIC(8,2)	Recorded clinical measurement

Tablesresearch_sitespatientspatient_measurements