Rolling Default Rates SQL | Dataford Interview Questions

American Credit Acceptance wants to monitor default trends among recurring borrowers in its servicing portfolio. Write a PostgreSQL query that calculates monthly and rolling three-month default rates from borrower status records.

Requirements

Include only borrowers whose borrower_type is Recurring.
Aggregate valid loan statuses by calendar month, counting defaults and all known statuses.
Calculate the rolling three-month default rate as the total defaults divided by total known statuses across the current month and previous two available months.
Return one row per month, ordered chronologically. Exclude records with an unknown borrower, unknown status, or missing status from the rate calculation.

Schema

borrowers

Column	Type	Description
borrower_idPK	INTEGER	Unique borrower identifier
borrower_name	VARCHAR(100)	Borrower name
borrower_type	VARCHAR(20)	Borrower classification
origination_date	DATE	Loan origination date

loan_status_history

Column	Type	Description
status_idPK	INTEGER	Unique status record identifier
borrower_id	INTEGER	References borrowers.borrower_id
status_date	DATE	Date of the observed loan status
loan_status	VARCHAR(20)	Observed servicing status

Your question is Rolling Default Rates SQL. 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