SQL Rolling Average Window | Dataford Interview Questions

Business Context

The IRS Submission Processing program monitors how quickly processing centers complete tax returns. Analysts need a monthly view and a smoothed three-month trend for each center.

Task

Write a PostgreSQL query to calculate the monthly average processing time and the three-month rolling average for each IRS processing center.

Requirements

Include only returns with status = 'Processed' submitted from January through May 2025.
Aggregate processing time by processing center and calendar month.
Calculate a rolling average using the current month and up to the two preceding months within each center.
Return results ordered by center name and month.

Schema

irs_processing_centers

Column	Type	Description
center_idPK	INTEGER	Unique processing center identifier
center_name	VARCHAR(100)	IRS processing center name
state_code	CHAR(2)	State abbreviation for the center

tax_returns

Column	Type	Description
return_idPK	INTEGER	Unique tax return identifier
center_id	INTEGER	Processing center assigned to the return
submitted_at	DATE	Date the return was submitted
status	VARCHAR(20)	Current processing status
processing_days	INTEGER	Elapsed days required to process the return

Your question is SQL Rolling Average Window. 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