Given a table of loan repayments, how would you calculate the rolling 30-day default rate?
Assume a repayment is in default when defaulted is true. Calculate the rate for each date with at least one repayment, using the current date and prior 29 calendar days. Include rows with a null defaulted value in the repayment denominator but not in the default numerator.
repayment_date, repayment_count, default_count, and rolling_default_raterolling_default_rate to six decimal places| Column | Type | Description |
|---|---|---|
| repayment_idPK | INT | Unique repayment identifier |
| loan_id | VARCHAR(20) | Loan associated with the repayment |
| repayment_date | DATE | Date the repayment was recorded |
| defaulted | BOOLEAN | Whether the repayment is in default |
| repayment_amount | NUMERIC(12,2) | Amount associated with the repayment |