SQL Rolling 30-Day Claims Average | Dataford Interview Questions

Blue Cross and Blue Shield of Kansas needs a daily claims-volume trend for its active plan products. Write a PostgreSQL query that calculates the trailing 30-calendar-day average number of finalized claims for each active plan.

Requirements

Count claims with status PAID or DENIED; exclude PENDED and VOID claims.
Include calendar dates with zero claims so the average represents 30 calendar days rather than only days containing claims.
Calculate the rolling average separately for each active plan using the current date and preceding 29 dates.
Return results for January 31, 2025 through February 5, 2025, ordered by plan code and date. Round the average to four decimal places.

Schema

plan_products

Column	Type	Description
plan_codePK	VARCHAR(20)	Unique plan product identifier
plan_name	VARCHAR(100)	Plan product display name
active_from	DATE	Product activation date
active_to	DATE	Product deactivation date

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
plan_code	VARCHAR(20)	Associated plan product code
service_date	DATE	Date of service
status	VARCHAR(20)	Claim adjudication status

Your question is SQL Rolling 30-Day Claims 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