Rolling Average Admissions SQL

MediumSQL · PostgreSQL

Problem

Stanford Medicine wants to monitor monthly patient admissions by hospital department. Write a PostgreSQL query that calculates a rolling three-month average for every department, including departments or months with no admissions.

Requirements

Generate monthly rows for January through March 2024 for every department.
Aggregate duplicate admission records within each department and month, treating missing admission counts as zero.
Use a window function partitioned by department and ordered by month. The first month should average one month, the second should average two months, and the third should average all three months.
Return department name, month, monthly admissions, and the rolling average rounded to two decimal places. Sort by department ID and month.

Representative Data

The data includes duplicate monthly records, a null admission count, departments without admissions, and an admission with no matching department.

Schema

hospital_departments

Column	Type	Description
department_idPK	INTEGER	Unique hospital department identifier
department_name	VARCHAR(100)	Stanford Medicine department name

monthly_admissions

Column	Type	Description
admission_idPK	INTEGER	Unique admission aggregate record identifier
department_id	INTEGER	Referenced department identifier
admission_month	DATE	First day of the month represented by the record
admission_count	INTEGER	Number of admissions in the record

Tableshospital_departmentsmonthly_admissions