LEFT JOIN + HAVING with date math
Quarterly dormancy review. Anchor date is 2025-12-01. I want every Open account whose last posted transaction was more than 180 days before the anchor — that's last_post < '2025-06-04'. EXCLUDE accounts that have never transacted at all (those are a different escheatment workstream). Return `account_id`, `customer_id`, `product_subcategory`, and `last_txn_date`, sorted by last_txn_date ascending so the deepest dormancy lands at the top of the queue.
| Column | Type | Key |
|---|---|---|
| account_id | INT | PK |
| customer_id | INT | FK → retail_customers |
| product_id | INT | FK → retail_products |
| branch_id | INT | FK → retail_branches |
| open_date | TEXT | |
| close_date | TEXT | |
| status | TEXT | |
| ownership_type | TEXT | |
| interest_rate | REAL | |
| original_balance | REAL | |
| credit_limit | REAL | |
| fico_at_orig | INT | |
| maturity_date | TEXT |
| Column | Type | Key |
|---|---|---|
| txn_id | INT | PK |
| account_id | INT | FK → retail_accounts |
| branch_id | INT | FK → retail_branches |
| post_date | TEXT | |
| channel | TEXT | |
| transaction_type | TEXT | |
| debit_credit | TEXT | |
| amount | REAL | |
| description | TEXT | |
| counterparty_name | TEXT | |
| is_cash_flag | INT | |
| is_reversal | INT |
| Column | Type | Key |
|---|---|---|
| product_id | INT | PK |
| product_code | TEXT | |
| product_name | TEXT | |
| category | TEXT | |
| subcategory | TEXT | |
| default_apr | REAL | |
| default_apy | REAL | |
| monthly_fee | REAL | |
| nsf_fee | REAL | |
| od_fee | REAL |
Each hint you reveal reduces the XP you can earn. Try the query first.
Each account's last activity is the maximum post_date across the transactions table. LEFT JOIN account → transactions so accounts with zero transactions survive the join, then aggregate per account.
Open status only — closed accounts aren't dormant, they're closed. Bring product_subcategory in via a join to retail_products for the output column.
The dormancy filter belongs in HAVING because it references the per-account aggregate. Two conditions ANDed: the max post_date is not NULL (excludes never-transacted) AND it's strictly before the cutoff date the briefing gave.