Conditional COUNT pivot across the active book, sliced by product subcategory
Standing aging report for the servicing committee. Pivot the active book by `current_status` across product subcategory. Seven columns: `subcategory`, `current_count`, `dpd_30`, `dpd_60`, `dpd_90`, `charge_offs`, `total_active`. Active means anything except `paid_off` or `refinanced` — those loans aren't ours to service anymore. Sort by `subcategory` ascending.
| Column | Type | Key |
|---|---|---|
| origination_id | INT | PK |
| app_id | INT | FK → lending_applications |
| account_id | INT | FK → retail_accounts |
| funded_date | TEXT | |
| funded_amount | REAL | |
| term_months | INT | |
| interest_rate | REAL | |
| rate_type | TEXT | |
| arm_reset_date | TEXT | |
| fico_at_orig | INT | |
| dti_at_orig | REAL | |
| ltv_at_orig | REAL | |
| property_value | REAL | |
| lo_name | TEXT | |
| channel | TEXT | |
| current_status | TEXT | |
| closed_date | TEXT |
| 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.
Three-table JOIN to reach product subcategory: originations → applications → products.
Active-book filter: exclude the two terminal closed states (paid-off and refinanced). Both are off your books; including them dilutes every bucket count and the rates in the next slide.
Each delinquency-bucket column is a conditional SUM gated on a single status value. `total_active` is just COUNT(*) over the same active-filter — useful as the proportion-denominator for the rate slide that follows this one.