Multi-table JOIN across application + geography + customer dimensions, with derived universal_loan_id and rounded amount per FFIEC LAR spec
Mock LAR row reconstruction — the format I need to hand to the FFIEC submission tool. For the first 20 applications by application_date, build a row with these 14 columns: `universal_loan_id` (concat of `app_id`, application_date, tract_id with hyphens), `application_date`, `action_taken`, `loan_purpose`, `loan_amount_rounded` (requested_amount rounded to nearest thousand, integer), `borrower_state`, `property_county`, `property_state`, `ethnicity`, `race`, `sex`, `age_bucket`, `lmi_flag`, `minority_pct` (rounded 2 decimals). Sort by application_date ascending, then app_id ascending. LIMIT 20.
| 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 |
|---|---|---|
| tract_id | TEXT | PK |
| msa_code | TEXT | |
| county | TEXT | |
| state | TEXT | |
| lmi_flag | INT | |
| minority_pct | REAL |
| 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.
Three-table JOIN — applications, customers (for borrower_state), geography (for property_state, county, lmi_flag, minority_pct). INNER JOIN on geography excludes apps without a property tract — non-mortgage products, which match HMDA scope.
Build `universal_loan_id` by string-concatenating three application-level fields with a separator character. Real HMDA ULIDs are 23-char hashes; here the analyst's job is to construct a unique-per-record string that the FFIEC submission tool accepts in that position.
`loan_amount_rounded` follows FFIEC's privacy rule — round to nearest $1,000 so exact balances can't be re-identified from the public LAR. Two-step formula: divide by 1000 and round to integer, then multiply back by 1000. Cast to int; the FFIEC schema rejects decimals. LIMIT 20 after the sort.