Claims Rolling Average by Region | Dataford Interview Questions

New York Life wants to monitor claims-processing volume across its policy administration regions. Given policy claim transactions and regional metadata, write a PostgreSQL query that reports monthly processed claims and a rolling three-month average for each region.

Requirements

Join claims to the regional reference table and include only claims with status = 'PROCESSED' and a non-null processing date.
Aggregate the number of processed claims by region and calendar month for 2024.
Calculate a rolling average using the current month and the two preceding reported months within each region.
Return results ordered by region name and month, with the rolling average rounded to two decimal places.

The rolling window should use reported monthly rows. A month with no processed claims is not emitted by this query.

Schema

claims

Column	Type	Description
claim_idPK	INT	Unique claim transaction identifier
region_id	INT	Region responsible for processing the claim
status	VARCHAR(20)	Current claim processing status
processed_at	TIMESTAMP	Timestamp when claim processing completed

regions

Column	Type	Description
region_idPK	INT	Unique regional identifier
region_name	VARCHAR(50)	New York Life operating region name

Your question is Claims Rolling Average by Region. 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