CASE-based bucketing on a derived date-difference column. AR aging is the canonical operations diagnostic for the AR team — every Monday, the AR manager runs this view to see how much money is sitting in each age bucket and where collection effort needs to go. Combines date arithmetic, CASE bucketing, and aggregation across two metrics (count + dollars).
Monday AR review — I need open AR bucketed by age. Open AR = claims still in the work queue (claim_status IN ('submitted', 'appealed')); they haven't reached a final paid/closed/denied resolution. Compute days_outstanding = julianday('2026-04-30') - julianday(submission_date), then bucket: 0-30, 31-60, 61-90, 90+. Three columns: aging_bucket, open_claims (count), open_amount (SUM of submitted_amount, rounded to 2 decimals). Sort by aging_bucket so the report reads chronologically. The 90+ bucket is where the conversation focuses — claims that old usually mean stalled appeals or missed timely-filing windows.
| Column | Type | Key |
|---|---|---|
| claim_id | INT | PK |
| charge_id | INT | FK → fact_charges |
| payer_id | INT | FK → dim_payer |
| submission_date | TEXT | |
| claim_status | TEXT | |
| submitted_amount | REAL |
Each hint you reveal reduces the XP you can earn. Try the query first.
Filter first — claim_status IN ('submitted', 'appealed') keeps only the open AR. The other statuses (paid, closed, denied) are already-resolved AR and shouldn't appear in the aging view.
Compute days_outstanding = julianday('2026-04-30') - julianday(submission_date). julianday is SQLite's date-difference function; the result is a number of days you can compare to bucket thresholds in a CASE expression.
CASE WHEN ... THEN ... ELSE ... END is the bucketing pattern. Group by the derived bucket label, aggregate the count + dollar sum. Sort the buckets in chronological order — '0-30' first, '90+' last; ASC sort works because the labels happen to sort in the right order, but a CASE-based ORDER BY is safer.