Multi-table JOIN with date arithmetic on a TEXT date column + NULL filter on a planted defect
Pre-emptive outreach list. Pull every ARM whose first reset hits in the next 6 months — `arm_reset_date` between 2026-04-29 and 2026-10-29 inclusive. Six columns: `origination_id`, `customer_name` (concat first + last), `funded_amount`, `interest_rate`, `arm_reset_date`, `days_until_reset` (integer days from today). Sort by `arm_reset_date` ascending, then `origination_id` ascending. Heads up — the data quality team flagged 3 ARM rows with NULL `arm_reset_date`; those slip through your filter and need a separate ticket. Don't include them here.
| 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 |
|---|---|---|
| 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 — originations → applications → customers — to pull the borrower's name alongside the loan facts. Postgres string concatenation is `||`; pair it with a literal space to format the name.
Three filters compose: rate_type = ARM, the date window on arm_reset_date, AND an explicit `arm_reset_date IS NOT NULL`. The IS NOT NULL is doing two jobs — it excludes the planted defect rows the briefing flagged, and it keeps downstream date arithmetic from throwing on NULL inputs.
`arm_reset_date` is stored as TEXT — cast to DATE before the date-window comparison and before the subtraction. Postgres date subtraction returns an integer day count, which is exactly what `days_until_reset` should look like.