AVG aggregation with GROUP BY across a JOIN of fact and application tables
Refreshing the LO comp grid this morning. Pull the average funded amount per `loan_purpose` from the funded book — that's `lending_originations` joined to `lending_applications`. Two columns: `loan_purpose`, `avg_funded` (rounded to 2 decimals). Sort by avg_funded descending so the highest-ticket purposes lead.
| 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 |
Each hint you reveal reduces the XP you can earn. Try the query first.
Purpose lives on `lending_applications`; funded amount lives on `lending_originations`. JOIN on the link key that uniquely ties one origination to one application — joining on the wrong key fans the rows out and inflates every average.
AVG with rounding so the column reads as a dollar amount in the deck. Group by the purpose dimension.
Two-column projection. Sort highest average first so the high-ticket purposes lead the LO comp grid.