Rolling 30-Day Provider Average

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

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

Output details

• Round rolling_30_day_avg to exactly 2 decimal places.
• Return NULL for rolling_30_day_avg when the 30-day window contains no non-NULL claim amounts.
• Include zero-valued claim amounts in the average.