JOIN to customer attributes + CASE bucketing
Pre-read for the consumer banking strategy meeting. The CFPB found that ~9% of accounts pay ~79% of NSF/OD fees and the average fee is ~$35. I want the same view on our book — break NSF and OD fee revenue by customer FICO band: <600, 600-679, 680-739, 740+, plus a 'No FICO' bucket for the customers we couldn't pull a score on. Exclude waived fees (`waived_flag = 0`). Three columns: `fico_band`, `fee_events`, `fee_revenue` (rounded to 2 decimals). Sort by band low-to-high so the deck reads ascending.
| Column | Type | Key |
|---|---|---|
| fee_id | INT | PK |
| account_id | INT | FK → retail_accounts |
| post_date | TEXT | |
| fee_code | TEXT | |
| amount | REAL | |
| waived_flag | INT |
| 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 |
|---|---|---|
| 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.
Fee events are in `retailops_fees` but the FICO score lives on the customer. You'll need to walk fees → accounts → customers to get there. NSF and OD are two of the fee_codes; filter to those two only.
Build the band with CASE WHEN on `fico_current` — order branches tightest to widest. The NULL case (customers with no FICO pull) needs its own ELSE branch, otherwise those rows drop silently when you GROUP BY.
GROUP BY the band, COUNT(*) for events, SUM(amount) for revenue, ROUND the dollar column to 2. Sort the bands ascending — prefix labels with a digit ('1_<600', '2_600-679', ...) so ORDER BY puts them in score order rather than alphabetical.