Rolling Average Acceleration Flag | Dataford Interview Questions

Write a SQL query using window functions to calculate a 7-day rolling average of claim costs by claim and flag claims whose cost trend accelerates.

Use the claim_costs table. Treat acceleration as a positive increase in the rolling average that is larger than the preceding increase. Preserve rows with null costs.

Output

One row per claim and cost date, ordered by claim_id, then cost_date.
Columns: claim_id, cost_date, daily_cost, rolling_7_day_avg, and acceleration_flag.
acceleration_flag must be a Boolean.

Schema

claim_costs

Column	Type	Description
claim_cost_idPK	INT	Unique cost observation identifier
claim_id	INT	Claim identifier
cost_date	DATE	Date of the claim cost observation
daily_cost	DECIMAL(12,2)	Cost recorded for the claim on the observation date

Your question is Rolling Average Acceleration Flag. 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