CASE bucketing across credit-policy thresholds + window-function share-of-total
Underwriting policy review next week. Bucket every funded origination by `dti_at_orig` into four bands and report the share. Bands: `0.20-0.29`, `0.30-0.36`, `0.37-0.43`, `>0.43`. The 0.43 line is the QM-rule threshold — anything above is non-QM and books differently. Three columns: `dti_band`, `loan_count`, `pct_of_book` (4 decimals). Sort by `dti_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 |
Each hint you reveal reduces the XP you can earn. Try the query first.
CASE bucket on `dti_at_orig`. Reuse the prefixed-label trick from M11 to control lex-sort. Mind the boundary on the QM cutoff: the briefing wants 0.43 inclusive on the QM-side band and strictly above on the non-QM band — that boundary convention is what audit will check.
Single-table query — `dti_at_orig` is on the originations row, no JOIN needed. (The concept tag mislabels this as a JOIN task — ignore it.)
For pct_of_book, same aggregate window function pattern as M12 — count divided by the grand total computed over the un-partitioned window. Round to 4 decimals.