Paid order CTE decomposition

Using a CTE for paid or shipped orders, return buyer_id and revenue (the summed total_amount per buyer). Order by revenue descending, then buyer_id ascending.

Start with a paid_orders CTE, then a buyer_revenue CTE that groups it, then expose the result.

Sample input

ordersPaid/shipped orders feed the metric; pending/refunded are filtered out in the first CTE.

order_id	buyer_id	status	total_amount
1001	1	paid	80
1004	1	shipped	220
1009	1	pending	30
1005	2	paid	540
1010	6	paid	640

Expected output

Expected outputRevenue per buyer over paid/shipped orders only (buyer 1 is 300, not 330). Highest first.

buyer_id	revenue
2	650
6	640
1	300
3	290
4	150

Constraints

Use at least one CTE. The first layer filters status IN ('paid', 'shipped'); the second aggregates SUM(total_amount) per buyer. Order by revenue DESC, buyer_id ASC.

Expected skills

CTE decomposition, grouped aggregation, and readable intermediate naming.

SQL

AI evaluation

Run the SQL query to inspect preview rows.