Rolling Average Processing Times | Dataford Interview Questions

Guidewire ClaimCenter analysts want to monitor how claim processing times change over time. Write a PostgreSQL query that calculates a three-claim rolling average for completed claims, grouped by claim type.

Requirements

Join claims to policyholders and include only claims with a matching policyholder and status = 'Closed'.
Calculate processing_days as the number of calendar days between opened_date and closed_date.
Calculate the average processing time for the current claim and the two preceding closed claims of the same claim_type, ordered by closed_date and claim_id.
Return the claim number, claim type, region, closed date, processing days, and rolling average, rounded to two decimal places. Sort by claim type and closed date.

Schema

claims

Column	Type	Description
claim_idPK	INT	Primary key for the claim
claim_number	VARCHAR(20)	ClaimCenter claim identifier
policyholder_id	INT	References policyholders.policyholder_id
claim_type	VARCHAR(30)	Claim classification
status	VARCHAR(20)	Current claim status
opened_date	DATE	Date the claim was opened
closed_date	DATE	Date the claim was closed

policyholders

Column	Type	Description
policyholder_idPK	INT	Primary key for the policyholder
policyholder_name	VARCHAR(100)	Policyholder full name
region	VARCHAR(30)	Policyholder region

Your question is Rolling Average Processing Times. 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