Rolling Enrollment Across Sites | Dataford Interview Questions

Business context

Dana-Farber Cancer Institute's clinical research teams monitor enrollment across trial sites to identify changes in recruitment pace. You are given site metadata and patient enrollment records.

Task

Write a PostgreSQL query that reports monthly enrollment and a rolling three-month average for every active trial site from January through April 2025.

Requirements

Aggregate patient_count by site and calendar month, treating NULL counts as zero.
Include active sites for every month in the requested period, including months with no enrollment records.
Use a window function partitioned by site and ordered chronologically to calculate the average of the current month and up to the two preceding months.
Return results ordered by site name and month.

Schema

trial_sites

Column	Type	Description
site_idPK	INT	Unique trial site identifier
site_name	VARCHAR(100)	Clinical trial site name
trial_code	VARCHAR(20)	Clinical trial identifier
site_status	VARCHAR(20)	Current operational status of the site

enrollment_records

Column	Type	Description
enrollment_idPK	INT	Unique enrollment record identifier
site_id	INT	References the site receiving enrollment activity
enrollment_date	DATE	Date of enrollment activity
patient_count	INT	Number of patients enrolled in the record

Your question is Rolling Enrollment Across Sites. Start with the requirements and the two 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