Conditional aggregation across an event table
Quarterly ATM economics review. Roll up `retailops_atm_events` into two rows — one for in-network usage (our own ATMs, no surcharge) and one for foreign-network usage (Allpoint/MoneyPass/etc., where we collect a surcharge from the user but pay the network back). For each, show `network_type` ('In-Network' / 'Foreign'), the event count, total cash dispensed (sum of amount), and surcharge revenue (sum of surcharge_amount). Round dollar columns to 2 decimals. Order with In-Network first.
| Column | Type | Key |
|---|---|---|
| event_id | INT | PK |
| account_id | INT | FK → retail_accounts |
| atm_id | TEXT | |
| event_ts | TEXT | |
| in_network_flag | INT | |
| amount | REAL | |
| surcharge_amount | REAL |
Each hint you reveal reduces the XP you can earn. Try the query first.
The `in_network_flag` is a 0/1 column on `retailops_atm_events`. Two rows in the output — one per value — so the GROUP BY is just on the flag (or on a CASE that maps it to a label).
Use a CASE in the SELECT to convert the 0/1 into the human-readable 'In-Network' / 'Foreign' label, then GROUP BY the same expression (or by `in_network_flag` directly). Either works in Postgres.
Aggregate three measures: COUNT(*) for events, SUM(amount) for the cash volume, SUM(surcharge_amount) for the surcharge revenue. Round the two dollar columns. Order by `in_network_flag` DESC so In-Network lands above Foreign.