Rolling Average Claims by Adjuster | Dataford Interview Questions

Write a SQL query using window functions to find the rolling 30-day average of claims processed per adjuster at Sedgwick.

Use the provided claims and adjusters data. Include only claims with a processed status and a non-null processing date. Calculate the average of each adjuster's daily processed-claim counts over the current date and preceding 29 calendar days.

Output

One row per adjuster and date with at least one processed claim.
Columns: adjuster_id, adjuster_name, processed_date, claims_processed, and rolling_30_day_avg.
Order by adjuster_id, then processed_date ascending.

Schema

adjusters

Column	Type	Description
adjuster_idPK	INT	Unique identifier for the adjuster
adjuster_name	VARCHAR(100)	Full name of the adjuster
team_name	VARCHAR(100)	Adjuster team or operating group

claims

Column	Type	Description
claim_idPK	INT	Unique identifier for the claim
adjuster_id	INT	Adjuster assigned to the claim
processed_date	DATE	Date the claim was processed
status	VARCHAR(30)	Current claim processing status

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