Rolling 30-Day Defaults by Region | Dataford Interview Questions

Using a window function, how would you calculate the rolling 30-day average of loan defaults per region?

Use the regions and loan_defaults tables. Include only records whose status is DEFAULTED. The average should consider default counts by region and default date over the current date and preceding 29 calendar days.

Output

One row per region and date with at least one default.
Columns: region_name, default_date, daily_defaults, and rolling_30_day_avg.
Sort by region_name, then default_date ascending.

Schema

regions

Column	Type	Description
region_idPK	INT	Unique regional identifier
region_name	VARCHAR(50)	Name of the lending region

loan_defaults

Column	Type	Description
default_idPK	INT	Unique default event identifier
region_id	INT	Region associated with the loan
default_date	DATE	Date of the loan status event
default_status	VARCHAR(20)	Status of the loan event

Your question is Rolling 30-Day Defaults by Region. 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