Multi-table JOIN with a threshold-comparison filter and a derived basis-point column
Pricing-exception desk wants the weekly anomaly list. Surface every funded origination whose `interest_rate` exceeds the product's `default_apr` benchmark by more than 75bp. Six columns: `origination_id`, `product_code`, `funded_amount`, `interest_rate`, `default_apr`, `rate_premium_bp` (the premium expressed in basis points, integer). Sort by rate_premium_bp descending, then origination_id ascending for tie-break.
| 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 reach: originations → applications → products. Two JOINs. The benchmark APR lives on `retail_products`; you need it alongside each origination to compute the premium.
"More than 75bp above benchmark" is a unit-conversion problem. Basis points are 1/100th of a percentage point — translate the bp threshold into a decimal and add it to the benchmark column. Strict `>` not `>=`; the briefing says "more than," which excludes exactly-75bp loans.
The premium column re-expresses the same delta in basis points: multiply the rate gap by 10000 and ROUND to an integer for a clean column type. Six projected columns, two-key sort (premium desc, id asc).