SQL Revenue Analysis Practice: Paid Orders, Refunds & GROUP BY

Build a small report from six orders. The exercise definition is simple: count paid orders and subtract their refunds; exclude cancelled orders.

By QuerySprint project team · Published 19 September 2026 · Fictional examples, reproducible calculations

The fictional orders table

ID	Customer	Country	Status	Revenue	Refund
1	Ada	DE	paid	120	0
2	Bo	FR	paid	80	20
3	Ada	DE	cancelled	50	0
4	Cy	DE	paid	90	90
5	Di	FR	paid	200	0
6	Bo	FR	paid	40	0

1. Select paid orders

SELECT id, customer
FROM orders
WHERE status = 'paid'
ORDER BY id;

Expected IDs: 1, 2, 4, 5 and 6. Order 4 remains a paid order even though it was fully refunded. Order 3 is cancelled and must not contribute to this exercise.

2. Calculate net revenue

SELECT SUM(revenue - refund) AS net_revenue
FROM orders
WHERE status = 'paid';

Expected result: 420. The paid gross amounts total 530 and refunds total 110. Counting the cancelled order would incorrectly return 470.

3. Report by country

SELECT country, SUM(revenue - refund) AS net_revenue
FROM orders
WHERE status = 'paid'
GROUP BY country
ORDER BY net_revenue DESC, country ASC;

Expected rows: FR = 300, then DE = 120. The second sort key makes equal totals deterministic. The browser checks queries against a second dataset too, so a hard-coded 420 is not a solution.

What this teaches, and what it does not

Practice WHERE before aggregation, SUM of a row-level expression, GROUP BY and a stable ORDER BY. This toy definition is not a revenue-recognition policy. Real finance reports may require tax, currencies, settlement dates, partial refunds and duplicate prevention. SQLite runs locally; there is no production database connection.

Syntax reference: SQLite SELECT documentation.