Rolling Default Rate Query | Dataford Interview Questions

American Credit Acceptance wants to monitor recent credit performance by loan origination cohort. Write a PostgreSQL query that calculates the rolling 30-day default rate for each cohort and reporting date.

A loan enters a default event on the first date where its status is DEFAULT. The rolling window includes the reporting date and the preceding 29 calendar days.

Requirements

Derive each loan cohort from the month of origination_date.
Identify each loan's first default date, ignoring later repeated default statuses.
Produce one row per cohort and reporting date where at least one loan in that cohort has originated by that date.
Calculate cohort loan count, the number of first defaults during the rolling 30-day window, and the default rate as rolling defaults divided by cohort loan count.
Use 0 for cohorts with no defaults in the window, round the rate to four decimal places, and order by cohort month and reporting date.

Schema

loans

Column	Type	Description
loan_idPK	BIGINT	Unique loan identifier
origination_date	DATE	Date the loan was originated
product_code	VARCHAR(20)	American Credit Acceptance loan product code

loan_status_daily

Column	Type	Description
status_idPK	BIGINT	Unique status observation
loan_id	BIGINT	Loan associated with the status
status_date	DATE	Date of the status observation
status_code	VARCHAR(20)	Observed loan status

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