SCD2 change boundaries

Return buyer_id, paid_revenue (sum of total_amount where status is paid), and refunded_amount (sum of total_amount where status is refunded) for each buyer. Order by buyer_id ascending.

GROUP BY buyer_id; SUM(CASE WHEN status = 'paid' THEN total_amount ELSE 0 END) AS paid_revenue, and the same for refunded.

Sample input

ordersOnly buyer 6 has a refunded order (1006, $60). Shipped/pending orders contribute 0.

order_id	buyer_id	status	total_amount
1001	1	paid	80
1005	2	paid	540
1011	2	paid	110
1010	6	paid	640
1006	6	refunded	60

Expected output

Expected outputPaid revenue and refunded amount per buyer in one row. Buyer 4 has neither, so both are 0.

buyer_id	paid_revenue	refunded_amount
1	80	0
2	650	0
3	215	0
4	0	0
6	640	60

Constraints

GROUP BY buyer_id. Use SUM(CASE WHEN status = 'paid' THEN total_amount ELSE 0 END) and the same pattern for refunded. Every buyer appears once, with 0 where a status is absent.

Expected skills

Conditional aggregation: a CASE inside SUM to compute several metrics in one pass.

SQL

AI evaluation

Run the SQL query to inspect preview rows.