Rolling 7-Day Average with Window Functions

A clinical research company’s 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

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

Output details

• Return columns in this order: site_name, patient_id, patient_name, measurement_date, measurement_value, rolling_7_day_avg, site_rank.
• Round rolling_7_day_avg to two decimal places.
• Use the greatest measurement_date as latest, breaking ties with greatest measurement_id.
• Break site-ranking ties by ascending patient_id.
• Order rows by ascending site_id, patient_id, measurement_date, then measurement_id.