Multi-CTE with NOT EXISTS for negative-existence filter on payment history + CROSS JOIN to a scalar benchmark
Refi outreach list for the LO desk. Surface every current-status MTG-30Y origination priced at least 50bp above today's MTG-30Y benchmark, with NO late or missed payments in the last 6 months. Six columns: `origination_id`, `customer_name`, `funded_amount`, `interest_rate`, `benchmark_rate` (the MTG-30Y default APR), `premium_bp` (integer, basis points above bench). Sort by premium_bp DESC, origination_id ASC.
| 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 |
|---|---|---|
| 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 |
| 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.
Three composable pieces: a benchmark scalar (today's 30-year mortgage default APR), a target population (current-status mortgages priced above the benchmark by enough to matter), and a clean-payment-history gate. CTE-per-piece keeps each readable.
For the benchmark, return a single-row CTE pulling default_apr off `retail_products` for the right product. CROSS JOIN it to the targets so each row carries the benchmark without a correlated subquery in the WHERE.
For "no late or missed payments in the last 6 months," reach for `NOT EXISTS` against the payments table — correlate the inner query on origination_id, the recent-window date, and the bad-status set. NOT EXISTS reads cleaner than a LEFT JOIN + COUNT = 0 once multiple criteria compose. Premium_bp is the same bp formula from M14.