Multi-table EXISTS / NOT EXISTS + scoring + LIMIT
Q2 cash-back card campaign. I need the top 25 candidate customers — currently checking-only, not already on a credit card with us, FICO ≥ 680 (the floor underwriting will approve at), and Active status. From `retail_customers` joined through `retail_accounts` and `retail_products`. Output `customer_id`, `first_name`, `last_name`, `fico_current`, `estimated_income`, `segment`. Rank by `estimated_income` descending — higher-income transactors are the highest-value cash-back card targets. Limit 25.
| Column | Type | Key |
|---|---|---|
| customer_id | INT | PK |
| household_id | TEXT | |
| first_name | TEXT | |
| last_name | TEXT | |
| date_of_birth | TEXT | |
| state | TEXT | |
| segment | TEXT | |
| fico_current | INT | |
| estimated_income | REAL | |
| kyc_risk | TEXT | |
| acquisition_channel | TEXT | |
| onboarded_date | TEXT | |
| status | TEXT |
| Column | Type | Key |
|---|---|---|
| account_id | INT | PK |
| customer_id | INT | FK → retail_customers |
| product_id | INT | FK → retail_products |
| branch_id | INT | FK → retail_branches |
| open_date | TEXT | |
| close_date | TEXT | |
| status | TEXT | |
| ownership_type | TEXT | |
| interest_rate | REAL | |
| original_balance | REAL | |
| credit_limit | REAL | |
| fico_at_orig | INT | |
| maturity_date | TEXT |
| Column | Type | Key |
|---|---|---|
| product_id | INT | PK |
| product_code | TEXT | |
| product_name | TEXT | |
| category | TEXT | |
| subcategory | TEXT | |
| default_apr | REAL | |
| default_apy | REAL | |
| monthly_fee | REAL | |
| nsf_fee | REAL | |
| od_fee | REAL |
Each hint you reveal reduces the XP you can earn. Try the query first.
Two filters in one query are tricky: the customer must HAVE an open Checking account AND must NOT have any Credit Card account. EXISTS for the positive case, NOT EXISTS for the negative case — both correlated subqueries on the same `retail_accounts`.
Or use a CTE that aggregates per customer: `BOOL_OR(p.subcategory = 'Checking') AS has_checking`, `BOOL_OR(p.subcategory = 'Credit Card') AS has_card`, then filter `has_checking AND NOT has_card` in the outer SELECT.
Apply the FICO floor and Active-status filter from the briefing. ORDER BY estimated_income descending, then LIMIT to the campaign cap. Six columns in the briefing's order.