Multi-CTE with LEFT JOIN aggregation, then nested conditional aggregates with NULLIF/CASE guards for safe ratio math
Vintage charge-off review for the credit committee. By origination vintage year, report: total vintage size, charge-off count, charge-off rate, total unrecovered principal at time of CO, and principal-recovered rate (principal paid pre-CO ÷ funded amount across the CO cohort). Skip the 2019 ARM cohort — filter `funded_date >= '2024-11-01'`. Six columns: `vintage_year`, `vintage_size`, `charge_offs`, `co_rate` (4 decimals), `unrecovered_principal` (rounded 2 decimals), `principal_recovered_rate` (4 decimals). Sort by vintage_year 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.
First CTE rolls per-loan: LEFT JOIN payments and aggregate `principal_paid` for the non-CO statuses (paid plus late) so each origination row carries its total principal-paid-pre-CO. Use COALESCE inside the SUM so loans with no payments yet contribute zero rather than propagating NULL through the math.
Outer SELECT aggregates per vintage_year. The unrecovered-principal column is the loss amount per CO loan — funded minus principal-paid-pre-CO — summed only across charge-offs. Conditional SUM on a status filter is the canonical shape; the recovery-rate column is the inverse ratio (paid-pre-CO ÷ funded) summed across the CO cohort.
Guard the recovery-rate divide with a CASE on the denominator — a vintage with zero charge-offs has a zero denominator and would otherwise throw. Cast inner numeric expressions to NUMERIC before each ROUND; Postgres won't ROUND `double precision` to a fixed decimal count.