WHERE with date filter + set arithmetic across two events
Weekly retail dashboard. Net new checking = openings minus closures of `Checking` accounts during the trailing 7 days (use `2025-11-24` through `2025-11-30` inclusive). Single number, alias it `net_new_checking`. Pull from `retail_accounts` joined to `retail_products` for the subcategory filter.
| 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 |
|---|---|---|
| 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.
Two events sit in the same table: an opening (`open_date` falls in the window) and a closure (`close_date` falls in the window). One number is the difference between the two counts.
Conditional aggregation handles both legs in one SELECT — SUM a CASE that returns +1 for openings and -1 for closures, both gated on the date window and the Checking subcategory.
Don't filter on close_date IS NULL — closures during the window have a non-null close_date that you specifically want to count (negatively).