Conditional aggregation (CASE WHEN inside SUM) to compute approval ratio per product
First number for the credit committee tomorrow. Compute the approval rate per loan product — `action_taken = 1` (Originated) divided by total applications, grouped by product. Four columns: `product_code`, `approved_count`, `total_apps`, `approval_rate` (rounded to 4 decimals). Sort by approval_rate descending, then product_code ascending for tie-break.
| 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 |
|---|---|---|
| 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.
The ratio is two pieces: a numerator counting only approved rows, a denominator counting all rows in the same group. Both can come out of one aggregation pass — that's what conditional aggregation is for.
Postgres truncates integer division silently — int / int returns int. Cast at least one side of the divide to NUMERIC (or multiply by 1.0) before the round, or your rates will all come back as zeros.
Four projected columns, four-decimal rounding. Two-key sort (rate desc, code asc) keeps the ranking deterministic when two products tie on rate.