Running 30-Day Average of Claims | Dataford Interview Questions

GDIT healthcare claims analytics requires a daily view of prescription claim volume for each provider. Write a PostgreSQL query that calculates a rolling 30-day average, including calendar days with zero claims.

Requirements

Count prescription claims per provider and calendar date.
Include every provider for every date between the first and last valid prescription claim, treating missing daily activity as zero.
Use a window function partitioned by provider and ordered by date to calculate the average across the current day and preceding 29 calendar days.
For the supplied sample, return only the final calendar date, ordered by provider ID.

Schema

providers

Column	Type	Description
provider_idPK	INT	Unique provider identifier
provider_name	VARCHAR(100)	Provider display name

prescription_claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
provider_id	INT	Provider associated with the claim
claim_date	DATE	Date the claim was submitted
claim_type	VARCHAR(30)	Claim classification

Your question is Running 30-Day Average of Claims. 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