Rolling Default Rate by Vintage | Dataford Interview Questions

dv01's loan performance analytics team needs to monitor default behavior for the January 2024 consumer-loan vintage. Write a PostgreSQL query that calculates the rolling three-month default rate for that vintage from January through May 2024.

Requirements

Restrict the cohort to loans originated in January 2024.
Count each loan only once, in the first month where defaulted is true. Treat NULL performance values as not defaulted.
Include months with no defaults, and calculate the rolling three-month default rate as defaults in the current month and prior two months divided by the total size of the vintage.
Return one row per month, ordered chronologically, with the month, monthly defaults, rolling defaults, and rate rounded to four decimal places.

Schema

loans

Column	Type	Description
loan_idPK	INT	Unique consumer loan identifier
origination_month	DATE	Loan origination month
loan_purpose	VARCHAR(40)	Purpose of the consumer loan
borrower_state	VARCHAR(2)	Two-letter borrower state code

performance

Column	Type	Description
performance_idPK	INT	Unique monthly performance record
loan_id	INT	Referenced loan identifier
performance_month	DATE	Month of the performance observation
defaulted	BOOLEAN	Whether the loan defaulted during the observation month

Your question is Rolling Default Rate by Vintage. 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