Readable revenue query

Write a multi-CTE query that returns country and revenue: paid/shipped orders, joined to the buyer's country (missing country labelled 'unknown'), summed per country. Order by revenue descending, then country ascending.

paid_orders → orders_with_country (join + COALESCE) → country_revenue (group + sum) → expose.

Sample input

ordersPaid/shipped orders, joined to buyer country in a middle CTE.

order_id	buyer_id	status	total_amount
1001	1	paid	80
1004	1	shipped	220
1010	6	paid	640
1005	2	paid	540
1008	4	shipped	150

customersbuyer_id → country; buyer 2 has a NULL country (becomes unknown).

customer_id	country
1	US
2	null
3	GB
4	IN
6	GB

Expected output

Expected outputRevenue per country over paid/shipped orders, highest first. GB combines buyers 3 and 6.

country	revenue
GB	930
unknown	650
US	300
IN	150

Constraints

Use named CTEs (e.g. paid_orders, orders_with_country, country_revenue). Join orders to customers on buyer_id, COALESCE(country, 'unknown'). Keep the final SELECT boring. Order by revenue DESC, country ASC.

Expected skills

Layered CTEs, a join, COALESCE, and a clean final projection.

SQL

AI evaluation

Run the SQL query to inspect preview rows.