CASE bucketing on a continuous variable + conditional ratio across a JOIN
Risk committee asked for the approval-rate cut by FICO band. Bucket the bureau score into five bands — `<620`, `620-679`, `680-739`, `740-799`, `800+` — and report approval rate per band. Four columns: `fico_band`, `approved_count`, `total_apps`, `approval_rate` (4 decimals). Sort by `fico_band` ascending. Skip apps where the customer record has a NULL FICO — the bureau pull failed for those and they don't belong in this view. Also skip walk-in applicants (`customer_id IS NULL`) — they don't have a customer record to pull FICO from.
| 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 |
|---|---|---|
| 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 |
Each hint you reveal reduces the XP you can earn. Try the query first.
FICO is on the customer dim, not the application — JOIN through customer_id. Walk-ins (NULL customer_id) and rows with NULL FICO both belong outside the population per fair-lending convention; the briefing calls both out.
The bands are CASE-bucketed on `fico_current`. Build the labels so they lex-sort in ascending order — a numeric prefix on each label is the cleanest way to control sort without a helper column.
The approval-rate piece reuses M6's pattern exactly — conditional aggregation for the numerator, COUNT(*) for the denominator, NUMERIC cast before the divide to avoid integer truncation.