Cohort Default Rate SQL | Dataford Interview Questions

Affirm risk analytics needs to monitor default rates by loan origination cohort. Write a PostgreSQL query that calculates the default rate for Affirm loans originated during the six complete calendar months ending July 2025.

Use 2025-07-31 as the reporting date. A loan is defaulted when its latest status on or before the reporting date is default.

Requirements

Include cohorts from February 1 through July 31, 2025, grouped by origination month.
Use a CTE and ROW_NUMBER() to select each loan's latest status.
Return the cohort month, total originated loans, defaulted loans, and default rate.
Include loans without a status record, treating them as not defaulted. Order cohorts chronologically.

Schema

loan_applications

Column	Type	Description
application_idPK	INT	Application identifier
loan_id	INT	Affirm loan identifier
originated_at	DATE	Loan origination date
product	VARCHAR(40)	Affirm financing product
principal_amount	NUMERIC(12,2)	Original principal amount

loan_status_history

Column	Type	Description
status_idPK	INT	Status event identifier
loan_id	INT	Related loan identifier
status_date	DATE	Date of the status event
status	VARCHAR(20)	Loan status

Your question is Cohort Default Rate 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