Rolling 30-Day Provider Average | Dataford Interview Questions

Given a relational schema containing patient transaction records and provider registries, write a SQL query to calculate the rolling 30-day average of transactions per provider. Treat the metric as the average daily transaction count for the current date and prior 29 calendar days. Include every registered provider and every calendar date between the minimum and maximum transaction dates, including zero-activity dates.

Output

One row per provider and calendar date, ordered by provider_id, then report_date.
Columns: provider_id, provider_name, report_date, transaction_count, and rolling_30_day_avg.
Include registered providers without transactions and exclude transactions whose provider is not registered.

Schema

patient_transactions

Column	Type	Description
transaction_idPK	INTEGER	Unique patient transaction identifier
patient_id	INTEGER	Identifier of the patient associated with the transaction
provider_id	INTEGER	Provider identifier associated with the transaction
transaction_date	DATE	Calendar date on which the transaction occurred

provider_registry

Column	Type	Description
provider_idPK	INTEGER	Unique provider identifier
provider_name	VARCHAR(100)	Provider display name
registry_status	VARCHAR(20)	Current registry status

Your question is Rolling 30-Day Provider Average. 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