Your question is Moving Average of Monthly Defaults. Start with the requirements and the three 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.
Write a query using window functions to calculate the moving average of monthly loan defaults.
Use the calendar table so months with no defaults appear with a count of zero. Calculate a three-month moving average that includes the current month and up to the two preceding calendar months.
month_start, monthly_defaults, and three_month_moving_average.month_start ascending, and round the moving average to two decimal places.| Column | Type | Description |
|---|---|---|
| month_startPK | DATE | Calendar date representing a reporting month |
| fiscal_quarter | VARCHAR(30) | Fiscal quarter label for the month |
| Column | Type | Description |
|---|---|---|
| loan_idPK | INT | Unique loan identifier |
| origination_date | DATE | Date the loan was originated |
| borrower_state | VARCHAR(40) | Borrower's state |
| Column | Type | Description |
|---|---|---|
| default_idPK | INT | Unique default event identifier |
| loan_id | INT | Loan associated with the default event |
| default_date | DATE | Date of the default event |