Your question is Rolling 30-Day Defaults by Region. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
region_name, default_date, daily_defaults, and rolling_30_day_avg.region_name, then default_date ascending.| Column | Type | Description |
|---|---|---|
| region_idPK | INT | Unique regional identifier |
| region_name | VARCHAR(50) | Name of the lending region |
| 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 |