Multi-CTE with date arithmetic to derive seasoning, then CASE bucketing on LTV with multi-stat aggregation
ALCO portfolio review. Stratify the active mortgage book by `ltv_at_orig` band and report loan count, total balance, and average seasoning per band. Bands: `<0.60`, `0.60-0.79`, `0.80-0.89`, `>=0.90`. Active means `current_status` IN ('current','30dpd','60dpd','90dpd') AND product subcategory is Mortgage. Four columns: `ltv_band`, `loan_count`, `total_balance` (rounded 2 decimals), `avg_seasoning_months` (rounded 1 decimal). Sort by `ltv_band` 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 |
|---|---|---|
| app_id | INT | PK |
| customer_id | INT | FK → retail_customers |
| product_id | INT | FK → retail_products |
| application_date | TEXT | |
| requested_amount | REAL | |
| requested_term | INT | |
| loan_purpose | TEXT | |
| action_taken | INT | |
| denial_reason_1 | INT | |
| aus_recommendation | TEXT | |
| rate_spread | REAL | |
| lien_status | INT | |
| occupancy | INT | |
| ethnicity | INT | |
| race | INT | |
| sex | INT | |
| age_bucket | TEXT | |
| property_tract | TEXT | FK → lending_geography |
| Column | Type | Key |
|---|---|---|
| product_id | INT | PK |
| product_code | TEXT | |
| product_name | TEXT | |
| category | TEXT | |
| subcategory | TEXT | |
| default_apr | REAL |
Each hint you reveal reduces the XP you can earn. Try the query first.
First CTE filters the active mortgage book and derives seasoning per loan. Seasoning is "months since funded" — a date-difference in days from today's anchor (the briefing's reference date) to funded_date, scaled to months.
Outer SELECT: CASE bucket on `ltv_at_orig`. Reuse the M11/M13 prefix-label trick so the bands lex-sort in ascending order without a helper column.
Three aggregations in the outer SELECT — count, sum of balance, average seasoning. Cast inner expressions to NUMERIC before each ROUND, otherwise Postgres throws `function round(double precision, integer) does not exist`. That cast trips up almost every first attempt.