CASE expression inside GROUP BY to bucket raw values into analytically-meaningful groups
The board wants a one-liner on where the money flows. Roll the seven transaction channels into two buckets: 'cash' (any channel starting with `cash_`) and 'wire' (any channel starting with `wire_`). Ignore everything else — ACH, card, and online transfers go in a bucket we're not reporting today. Sum only positive (credit) amounts, exclude reversals. Return `channel_group` and `total_volume`, ordered by `total_volume DESC`. Two rows.
| Column | Type | Key |
|---|---|---|
| txn_id | INT | PK |
| account_id | INT | FK → aml_accounts |
| posted_date | TEXT | |
| posted_ts | TEXT | |
| amount | REAL | |
| channel | TEXT | |
| counterparty_name | TEXT | |
| counterparty_account_id | INT | |
| is_reversal | INT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Inside GROUP BY you can use a CASE expression, but the cleaner pattern is to compute the bucket in the SELECT list AND the GROUP BY, OR wrap in a subquery. The most readable form: put the CASE in the SELECT, alias it, then `GROUP BY 1` in dialects that support ordinal grouping.
`CASE WHEN channel LIKE 'cash_%' THEN 'cash' WHEN channel LIKE 'wire_%' THEN 'wire' ELSE NULL END` — and filter the NULLs out in WHERE using `channel LIKE 'cash_%' OR channel LIKE 'wire_%'`.
Don't forget `amount > 0 AND is_reversal = 0`. Withdrawals and reversals are negative (or flagged); they'll zero out your incoming volume if you include them.