DATE_TRUNC for vintage cohorts + conditional ratio across a JOIN
Servicing trend deck for the monthly review. Compute late-payment rate per origination cohort month — that's funded loans grouped by their `funded_date` month, then the share of payments where `status = 'late'`. Four columns: `vintage_month` ('YYYY-MM' format), `total_payments`, `late_payments`, `late_rate` (4 decimals). Skip the vintage 2019 ARM cohort — they're a separate book, filter `funded_date >= '2024-11-01'`. 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.
Two-table JOIN: originations for the funded_date / vintage, payments for the per-payment status. Filter the originations side to the main-band cohort before the JOIN so the 2019 ARM book doesn't dominate denominators.
For monthly buckets, reach for `DATE_TRUNC` to pin the bucket key to a first-of-month value. To render the 'YYYY-MM' label format, layer `TO_CHAR` on top — compose the two functions, don't try to substring the date.
Conditional ratio — same shape as M6 — with the late-status predicate as the numerator condition. Round to 4 decimals. The bucket expression you projected must match the GROUP BY exactly.