Rolling Average Claims Query | Dataford Interview Questions

Texas Mutual Insurance needs a monthly view of reported claims by business unit. Write a PostgreSQL query that includes months with no claims and calculates a rolling average across the current month and the two preceding months.

Requirements

Generate one row for every business unit and each month from January through March 2024.
Count reported claims by business unit and month, returning zero when no claims exist.
Use a window function partitioned by business unit to calculate the three-month rolling average, ordered chronologically.
Sort the final output by business unit name and month.

Schema

business_units

Column	Type	Description
business_unit_idPK	INTEGER	Unique business unit identifier
unit_name	VARCHAR(100)	Texas Mutual operating unit name

claims

Column	Type	Description
claim_idPK	INTEGER	Unique claim identifier
business_unit_id	INTEGER	Business unit associated with the claim
reported_at	DATE	Date the claim was reported
claim_status	VARCHAR(30)	Current claim status
incurred_amount	NUMERIC(12,2)	Reported incurred amount

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