Cohort definition with date arithmetic
I'm building the 2025 attrition deck. Pull every account that closed in calendar 2025 (close_date in 2025) and show its tenure in days. Five columns: `account_id`, `customer_id`, `product_subcategory`, `open_date`, `close_date`, `tenure_days` (close_date − open_date as an integer). Sort by close_date descending, then account_id ascending. The tenure number drives the cohort exit-rate-by-tenure-band analysis the deck builds on.
| 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.
The cohort is `close_date BETWEEN '2025-01-01' AND '2025-12-31'` — no NULL close_date filter needed because BETWEEN excludes NULLs in standard SQL.
Tenure in days is `close_date::date - open_date::date` in Postgres — that returns an integer count of days. Cast both columns since they're stored as TEXT.
Six columns in the order Sarah listed. ORDER BY close_date DESC, then account_id ASC for a stable secondary sort. Watch for any negative tenure values — those are data-quality bugs worth flagging.