GROUP BY a HMDA-coded integer column with COUNT
Standing Friday HMDA pre-check before I send anything to the LAR submission queue. Group `lending_applications` by `action_taken` and count rows. Two columns: `action_taken` (the integer code), `app_count`. Sort by action_taken ascending. The committee scans this every week — anything that looks off, we catch here.
| 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 |
Each hint you reveal reduces the XP you can earn. Try the query first.
Single-table aggregation. Group rows by the HMDA code column and count.
Leave the code as the raw integer — no CASE-based decoding. The LAR submission tool reads numeric codes, and the committee scans them at code-level too.
Sort the code ascending so the values list in order. Codes with zero rows simply won't appear in the result — that's a separate fair-lending question, not something to engineer around here.