Multi-CTE with ROW_NUMBER + HAVING to define a seasoned cohort, then conditional ratio across vintages
Vintage signal for the credit committee. Compute Early-Payment Default rate by `funded_date` month — a loan is EPD if any of its first 3 scheduled payments came in `late` or `missed`. Only count loans that have all 3 payments due (so the youngest vintages don't poison the rate). Four columns: `vintage_month`, `seasoned_originations` (count of loans with 3+ payments due), `epd_count`, `epd_rate` (4 decimals). Filter `funded_date >= '2024-11-01'` to skip the 2019 ARM cohort. Sort by vintage_month ascending.
| Column | Type | Key |
|---|---|---|
| origination_id | INT | PK |
| app_id | INT | FK → lending_applications |
| account_id | INT | FK → retail_accounts |
| funded_date | TEXT | |
| funded_amount | REAL | |
| term_months | INT | |
| interest_rate | REAL | |
| rate_type | TEXT | |
| arm_reset_date | TEXT | |
| fico_at_orig | INT | |
| dti_at_orig | REAL | |
| ltv_at_orig | REAL | |
| property_value | REAL | |
| lo_name | TEXT | |
| channel | TEXT | |
| current_status | TEXT | |
| closed_date | TEXT |
| Column | Type | Key |
|---|---|---|
| payment_id | INT | PK |
| origination_id | INT | FK → lending_originations |
| due_date | TEXT | |
| paid_date | TEXT | |
| scheduled_amount | REAL | |
| principal_paid | REAL | |
| interest_paid | REAL | |
| escrow_paid | REAL | |
| days_late | INT | |
| status | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
EPD is defined on each loan's first 3 scheduled payments — so the first job is identifying which payments those are, per loan. Reach for a window function that ranks payments by due_date partitioned per origination.
Two CTE-style steps compose the cohort: keep only the rank ≤ 3 rows, then gate the population to loans that have all 3 payments due using HAVING on the count. That HAVING is what enforces "seasoned" — youngest vintages with fewer than 3 payments due drop out so they don't poison the rate.
Per-loan EPD flag: any of the first 3 was bad → flagged. The bad-status set is what the briefing calls late or missed. Outer SELECT joins the cohort back to originations for the vintage label (DATE_TRUNC + TO_CHAR), then the conditional ratio computes EPD rate per month.