Final project · SQLite

Deliver regional net revenue

Your task

Return region and net_revenue for paid orders.
Preaggregate refunds per order. Include unmatched customer orders.
Sort net_revenue descending then region ascending.

Target: 80/100. Suggested active time: 10 minutes. You can keep working after that.

Show a hint

Use a refunds CTE and a left join.

View the scoring standard

Accuracy checks the values. Coverage checks the requested output. Time is recorded separately and never changes your score.

columns: 20 points

rows: 80 points

Tables and practice data

orders

order_id	customer_id	status	amount	region	ordered_at
1	1	paid	120	East	2026-09-01
2	2	pending	140	West	2026-09-02
3	1	paid	100	East	2026-09-03
4	3	paid	180	West	2026-09-03
5	99	paid	80	East	2026-09-04
6	NULL	cancelled	0	NULL	2026-09-04
7	2	paid	120	West	2026-09-05
8	4	paid	30	North	2026-09-06

customers

customer_id	name	email
1	Avery	team@example.test
2	Morgan	team@example.test
3	Riley	NULL
4	Casey	(empty)
5	Sky	sky@example.test

returns

return_id	order_id	refund
1	1	20
2	1	10
3	4	180
4	99	25

inventory

sku	on_hand	reorder_at
PEN	5	10
BOOK	20	10
MUG	0	5
BAG	NULL	4
CARD	4	4

events

event_id	customer_id	stage
1	1	visit
2	1	visit
3	1	checkout
4	2	visit
5	3	visit
6	3	checkout
7	4	visit

Download the input →

Your work

Active 0:00 · Paused 0s

SQL query

Import answer

Show the reference answer

WITH r AS (SELECT order_id,SUM(refund) AS refunded FROM returns GROUP BY order_id) SELECT o.region,SUM(o.amount-COALESCE(r.refunded,0)) AS net_revenue FROM orders o LEFT JOIN r ON o.order_id=r.order_id WHERE o.status='paid' GROUP BY o.region ORDER BY net_revenue DESC, o.region ASC

Avoid duplicating sales with multiple refunds. East net is 270, West 120, North 30.