By the end of this lesson you can calculate gross sales, discounts, net sales, refunds and net revenue from order-level data, state which orders count, and avoid the join that double-counts refunds.
Why it matters: Every finance pack starts with a revenue line, and most revenue arguments are really definition arguments: is it before or after discounts, are cancelled orders in, when do returns hit? Writing the definition into the query makes the number reproducible and auditable.
Dataset: Wadi Market online store. Examples run in the editor on the right.
Wadi Market (the fictional online retailer in the shop dataset) does not store an order total. Revenue has to be derived from the order lines, and finance needs to see each step separately, because each step answers a different question:
| Measure | Definition in this course | Question it answers |
|---|---|---|
| Gross sales | SUM(quantity * unit_price) over order lines | What would we have earned at the prices charged, before any discount? |
| Discounts | SUM(discount_amount) (already a line total, not per unit) | How much margin did promotions give away? |
| Net sales | gross sales − discounts | What did customers agree to pay for goods? |
| Refunds (returns) | SUM(refund_amount) from returns | How much did we give back for returned goods? |
| Net revenue | net sales − refunds | What did we keep? |
Shipping fees (orders.shipping_fee) are not product revenue. Report them as a separate line if you need them; mixing them in makes the discount rate and margin look wrong.
Which orders count
A revenue report must say which order statuses it includes. In this track we use one rule for shop: exclude cancelled and pending orders. Cancelled orders never became sales. Pending orders are unpaid cash-on-delivery or failed card payments, so no sale has happened yet. Orders that are paid, shipped, delivered or returned all count as sales; returned orders count as sales and their refunds are subtracted, which is exactly how a sale followed by a return appears in the books.
Use ordered_at for the period of the sale. It is a timestamp, so filter with a half-open range (>= '2025-01-01' AND < '2026-01-01') rather than BETWEEN … '2025-12-31', which would silently drop everything after midnight on 31 December.
SELECT <period or dimension>,
SUM(oi.quantity * oi.unit_price) AS gross_sales,
SUM(oi.discount_amount) AS discounts,
SUM(oi.quantity * oi.unit_price - oi.discount_amount) AS net_sales
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status NOT IN ('cancelled', 'pending')
GROUP BY <period or dimension>;SELECT EXTRACT(YEAR FROM o.ordered_at) AS year,
SUM(oi.quantity * oi.unit_price) AS gross_sales,
SUM(oi.discount_amount) AS discounts,
SUM(oi.quantity * oi.unit_price - oi.discount_amount) AS net_sales
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status NOT IN ('cancelled', 'pending')
GROUP BY year
ORDER BY year;One row per year (2023, 2024, 2025). Gross sales grow every year; discounts are a small share of gross (a little over 2%), so net sales sit just below gross. Check the identity on any row: gross_sales − discounts = net_sales.
Adding refunds without double counting
The returns table has one row per returned product within an order, keyed by (order_id, product_id), the same pair that identifies an order line. So a LEFT JOIN on both columns attaches at most one return to each line: lines that were not returned keep a NULL refund, which COALESCE(r.refund_amount, 0) turns into zero.
refund_amount is also NULL while a refund is still being processed. Treating that as zero means net revenue counts only refunds already paid. That is a policy choice, so say it in the report. A stricter finance team would accrue the pending refund instead (you will do that in the next lesson).
SELECT EXTRACT(YEAR FROM o.ordered_at) AS year,
SUM(oi.quantity * oi.unit_price - oi.discount_amount) AS net_sales,
SUM(COALESCE(r.refund_amount, 0)) AS refunds,
SUM(oi.quantity * oi.unit_price - oi.discount_amount)
- SUM(COALESCE(r.refund_amount, 0)) AS net_revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
LEFT JOIN returns AS r
ON r.order_id = oi.order_id
AND r.product_id = oi.product_id
WHERE o.status NOT IN ('cancelled', 'pending')
GROUP BY year
ORDER BY year;Still three rows. Net sales are identical to the previous example, which proves the LEFT JOIN on the full key added no rows. Refunds are a few percent of net sales, and net revenue is what remains.
| order_id | total |
|---|---|
| 1 | 100 |
| 2 | 250 |
| 3 | 80 |
| order_id | payment |
|---|---|
| 1 | failed card |
| 1 | mada |
| 2 | apple pay |
| 3 | split 1 |
| 3 | split 2 |
SELECT SUM(o.total) AS revenue
FROM orders AS o
JOIN payments AS p ON p.order_id = o.order_id;| order_id | total | payment |
|---|---|---|
| 1 | 100 | failed card |
| 1 | 100 | mada |
| 2 | 250 | apple pay |
| 3 | 80 | split 1 |
| 3 | 80 | split 2 |
The join repeats each order once per payment, so SUM(total) gives 610 instead of 430. Aggregate payments per order first (or sum from orders alone), then join.