Date arithmetic + multi-branch CASE for status aging
Compliance officer's daily review. Anchor date is 2025-12-01. From `retailops_disputes`, bucket every dispute by aging status: `resolved` (status starts with 'Resolved'), `overdue_provisional` (still open AND received more than 10 days before the anchor — past the 10-day provisional credit deadline), `approaching_10d` (open AND received 7–10 days before), `within_window` (open AND received less than 7 days before). Three columns: `aging_bucket`, `n_disputes`, `dispute_dollars` (rounded 2). Sort by aging_bucket ascending.
| Column | Type | Key |
|---|---|---|
| dispute_id | INT | PK |
| account_id | INT | FK → retail_accounts |
| received_date | TEXT | |
| dispute_amount | REAL | |
| dispute_type | TEXT | |
| status | TEXT | |
| resolved_date | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Date arithmetic: `('2025-12-01'::date - received_date::date)` returns an integer count of days. Cast both sides since `received_date` is stored as TEXT.
The CASE has four branches. Order matters: check `status LIKE 'Resolved%'` first (it covers both 'Resolved-Credited' and 'Resolved-Denied'), then the open-and-overdue branch, then the approaching window, then within_window as the default ELSE.
GROUP BY the aging_bucket expression. COUNT for n_disputes, SUM the dispute amount for the dollars (round to 2). Sort ascending — alphabetic ordering puts the buckets in a natural reading sequence.