Independent practice · SQLite

Calculate net paid amounts

Your task

Return order_id and net_amount for every paid order.
Sum multiple refunds per order before joining.
Missing refunds mean 0; amounts are whole dollars.

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

Show a hint

Use a CTE to group returns before the 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) refund FROM returns GROUP BY order_id) SELECT o.order_id,o.amount-COALESCE(r.refund,0) AS net_amount FROM orders o LEFT JOIN r ON o.order_id=r.order_id WHERE o.status='paid'

A direct join repeats the sale for each refund. Order 1 net is 90.