Rolling 30-Day Provider Average | Dataford Interview Questions

Given a table of medical claims, write a query to calculate the rolling 30-day average of claim amounts per provider.

Use the current claim date and the previous 29 calendar days. Exclude NULL claim amounts from the average, while retaining claims whose amount is NULL in the output.

Output

One row per claim, with claim_id, provider_id, claim_date, claim_amount, and rolling_30_day_avg
Include all claims, including claims with NULL providers or amounts
Order by provider_id ascending with NULL providers last, then claim_date ascending and claim_id ascending

Schema

medical_claims

Column	Type	Description
claim_idPK	INTEGER	Unique identifier for the medical claim
provider_id	INTEGER	Identifier of the healthcare provider
claim_date	DATE	Date on which the claim was submitted
claim_amount	NUMERIC(12,2)	Billed amount for the claim

Your question is Rolling 30-Day Provider Average. Start with the requirements and the one table 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