CASE aggregation to compute conditional percentages inside a GROUP BY
Follow-up on the FICO distribution: I need the approval RATE by band, not just the volume. For each band, show the application count and the pct approved (rounded to 2 decimals). Same bands as the last query. Call the columns `fico_band`, `n_apps`, and `pct_approved`. Sort low-FICO to high.
| Column | Type | Key |
|---|---|---|
| app_id | INT | PK |
| customer_id | INT | FK → banking_customers |
| product_type | TEXT | |
| app_date | TEXT | |
| decision | TEXT | |
| fico_at_app | INT | |
| offered_apr | REAL | |
| approved_limit | REAL | |
| booked_account_id | INT | FK → banking_accounts |
Each hint you reveal reduces the XP you can earn. Try the query first.
You already built the FICO bands in the previous mission. Now you need to add an approval rate column — think about how to express "what fraction of rows in this group met a condition" as a single aggregate expression.
Approval rate = fraction of apps where decision = 'Approved'. The idiomatic SQL: `AVG(CASE WHEN decision = 'Approved' THEN 1.0 ELSE 0 END) * 100`. Multiply by 100 to get a percentage, ROUND to 2 for a clean display.
Same band/order-by structure as the previous mission, plus a third column for approval rate. The AVG-of-CASE pattern from H2 collapses to a single aggregate per band — multiply by 100 and ROUND to 2 inside the SELECT.