Date-string slicing with substr() to roll fact_claims up to monthly buckets. Hospital RCM systems store dates as YYYY-MM-DD TEXT; carving year + month is the most common reporting transform for trend dashboards. Single-table aggregate with a derived column — a Medium step up from M4's flat per-payer count.
Sarah wants the field force trend for the QBR deck. From fact_claims, give me total claim counts rolled to the month — output 'YYYY-MM' format (e.g. '2024-08'), sorted ascending so the trend reads left to right. Two columns: month, claim_count. Use substr() to slice the submission_date string; the year and month carve fits in two substr calls concatenated. The 24-month window covers May 2024 through April 2026.
| 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.
substr() is the right tool — submission_date is TEXT in ISO-8601 (YYYY-MM-DD) shape, so positions 1-7 give you the YYYY-MM month key directly.
GROUP BY the derived month expression. COUNT(*) aliased as claim_count.
Sort ascending on month so the trend reads left-to-right; the QBR deck needs the time series in chronological order, not by volume.