A revenue report can be wrong even when every join and SUM is syntactically valid. This lesson calculates paid order value after recorded refunds and shows why refunds must be aggregated before joining. It also separates customer-level measures from transaction-level detail and explains the boundary choices a real reporting specification needs.
The fixture uses one currency and intentionally includes two refunds for the same order. That one-to-many relationship is the key test. A cancelled order is present to verify that the paid-order population is explicit. The calculations are reporting examples, not a recommendation for an accounting recognition policy.
Turn a business sentence into a metric contract
For this exercise, paid gross value means the amount on orders whose status is paid. Recorded refund value means the sum of refund amounts attached to those paid orders. Net paid value means paid gross minus those refunds. Cancelled orders do not enter any of these measures. That definition is deliberately narrow and testable.
A real request such as revenue this month may leave several questions unanswered: order date or payment date, refunds issued this month or lifetime refunds, taxes included or excluded, and one currency or converted values. Resolve these before selecting a date function. A mathematically correct total of the wrong population is still the wrong business answer.
Identify relationship grain before joining
The order table has one row per order; the refund table has one row per refund event. Order 1 has two refunds. Joining them directly repeats that order’s amount twice. SUM(order.amount) after that join overstates the gross value, even though each joined combination is a valid relationship match.
Reduce refunds to one row per order first, using GROUP BY order_id and SUM(amount). The resulting refund summary can be joined to each order without multiplying the order grain. Preserve the real refund count separately if needed. SUM(DISTINCT order.amount) is not a general fix because two genuinely different orders can have the same amount and both should contribute.
Define missing relationships and amounts
An order with no refund rows has no recorded refund in this model, so COALESCE(refund_total,0) implements that absence-as-zero policy. This differs from a refund row whose amount is unknown, which would be incomplete financial input. The fixture requires refund amounts with NOT NULL so its recorded refund totals are complete.
A production pipeline should count unknown amounts or invalid references rather than hiding them with zero fallbacks. A nullable customer link also needs a separate anonymous category if the customer report claims full coverage. The exercise uses required textual customer labels for simplicity; a normalised production schema would usually use stable customer keys and join labels afterwards.
Group customer measures without losing orders
After each paid order has one refund total, grouping by customer produces one customer summary. Ana has two paid orders, while Ben has one. The net values remain transaction-derived observations, even though the final output has customer grain. Keep order_count alongside monetary totals so readers can inspect the population being added.
A customer-name group can merge different people who share a label, so the real schema should group by a stable customer identity. The small fixture uses unique labels only to keep the demonstration focused on refund multiplication. If a report needs all customers, including those without paid orders, start from a customer dimension and left join the prepared measures.
Period boundaries and refund timing
A reporting period needs a date field that corresponds to the chosen metric. Filtering orders by order date and subtracting all later refunds gives a different result from filtering both events independently within the period. Neither is automatically the universal meaning of monthly net revenue. Name the report according to its explicit event and timing policy.
Use lower-inclusive and next-period-exclusive boundaries for timestamps, and define the reporting timezone. Repeated runs may need a snapshot cutoff when new refunds arrive during preparation. A staging table or consistent transaction can support a fixed report, but its operational rules depend on the engine. Record the as-of point instead of treating a changing live total as an immutable export.
Reconcile and expand the model
Check order-level nets before summing by customer. Paid order 1 has one hundred gross and thirty in refunds, order 2 has fifty with none, and order 4 has forty with five refunded. Their nets are seventy, fifty and thirty-five. Those values form an independent hand-check of the final one hundred fifty-five.
Then compare gross minus refund total with the sum of individual nets. The two should agree under this definition. Add tests for an equal-priced second order, multiple refunds, a cancelled order and an over-refund according to the domain’s rule. Currency conversion, taxes and partial payment events require an expanded model and metric contract, not a few unexplained CASE branches appended to the original total.
Run the complete practice setup
Four orders and three refunds demonstrate the important relationship. Ana’s order 1 has two separate refunds, and order 4 has one. Cara’s order is cancelled. All amounts are known and in the same hypothetical currency.
CREATE TABLE business_orders (id INTEGER PRIMARY KEY,customer VARCHAR(30) NOT NULL,amount DECIMAL(10,2) NOT NULL,status VARCHAR(20) NOT NULL);
CREATE TABLE business_refunds (id INTEGER PRIMARY KEY,order_id INTEGER NOT NULL,amount DECIMAL(10,2) NOT NULL);
INSERT INTO business_orders (id,customer,amount,status) VALUES
(1,'Ana',100,'paid'),(2,'Ben',50,'paid'),(3,'Cara',80,'cancelled'),(4,'Ana',40,'paid');
INSERT INTO business_refunds (id,order_id,amount) VALUES (1,1,10),(2,1,20),(3,4,5);Practical exercise and worked solution
Calculate the overall paid-order count, gross value, recorded refunds and net value from the same preaggregated relationship.
WITH refunds AS (
SELECT order_id,SUM(amount) AS refund_total FROM business_refunds GROUP BY order_id
)
SELECT COUNT(*) AS paid_orders,SUM(o.amount) AS gross,
SUM(COALESCE(r.refund_total,0)) AS refunded,
SUM(o.amount-COALESCE(r.refund_total,0)) AS net
FROM business_orders AS o LEFT JOIN refunds AS r ON r.order_id=o.id
WHERE o.status='paid';paid_orders | gross | refunded | net
3 | 190 | 35 | 155The three paid amounts are one hundred, fifty and forty. Recorded refunds against them are ten, twenty and five. Gross one hundred ninety minus thirty-five refunded equals net one hundred fifty-five, matching Ana’s net one hundred five plus Ben’s fifty. This reconciliation checks the same metric at order, customer and overall grains without assuming cancelled value belongs in the paid population.
Official documentation
Review the PostgreSQL grouping and joins guide, aggregate expressions and conditional expressions for the SQL operations used in this independently defined reporting model.
Real-World Business SQL Problems: Paid Orders, Refunds and Net Value Examples
Each example shows the code, the output it produces and what each part does.
Preaggregate refunds before customer net values
The refund CTE creates one summary per order. Ana’s two paid orders contribute one hundred forty gross and thirty-five refunded, leaving one hundred five. Ben contributes fifty and no recorded refund. The cancelled order is excluded by the stated population filter rather than included and corrected through a display label.
WITH refunds AS (
SELECT order_id,SUM(amount) AS refund_total FROM business_refunds GROUP BY order_id
)
SELECT o.customer,COUNT(*) AS paid_orders,
SUM(o.amount) AS gross,SUM(COALESCE(r.refund_total,0)) AS refunded,
SUM(o.amount-COALESCE(r.refund_total,0)) AS net
FROM business_orders AS o LEFT JOIN refunds AS r ON r.order_id=o.id
WHERE o.status='paid' GROUP BY o.customer ORDER BY o.customer;customer | paid_orders | gross | refunded | net
Ana | 2 | 140 | 35 | 105
Ben | 1 | 50 | 0 | 50Common Real-World Business SQL Problems: Paid Orders, Refunds and Net Value Mistakes
Adding the order amount after a raw refund join
Joining orders directly to refund events gives order 1 two rows. Summing its amount counts one hundred twice, inflating gross value. DISTINCT on amount can appear to help until two valid orders have equal prices. Fix the grain by preaggregating refunds or otherwise producing exactly one prepared monetary row per order before customer grouping.
Real-World Business SQL Problems: Paid Orders, Refunds and Net Value FAQ
Is net value always gross minus all refunds?
Only under the defined report policy. Timing, currency and recognition requirements can change the metric: refunds may be counted in the month they happen or against the original sale, and partial refunds and fees may be treated differently. Agree the definition before writing the query.
Why not use SUM DISTINCT to fix duplication?
Because equal amounts can belong to separate real orders: two different £20 orders are both revenue. Fix the join that duplicated rows, usually by aggregating each table before joining, rather than removing values that happen to be equal.
Should customers be grouped by name?
No. Use stable customer identity in a real schema, the customer id, because labels alone can merge different people with the same name and split one person whose name was typed differently. Show the name, but group by the key.
How do I check that a revenue report is correct?
Reconcile it: compare the total with an independent source, such as payment provider totals or the sum of invoice rows, and check that the parts, such as revenue by region, add up to the whole. Investigate any difference before the report is shared.
How should a report handle different currencies?
Never add amounts in different currencies directly. Either report each currency separately or convert using an agreed rate table, joined on currency and date, and state which rates were used. Store the original currency and amount alongside any converted value.
Real-World Business SQL Problems: Paid Orders, Refunds and Net Value Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
What is Ana’s net paid value under this lesson’s definition?
Real-World Business SQL Problems: Paid Orders, Refunds and Net Value Quiz
2 questions to check what you learned.
Why aggregate refunds by order before the join?
- To remove every refund event permanently
- To make all customers share one id
- To preserve one prepared row per order and avoid multiplying gross amounts
What makes a monthly revenue query well-defined?
- An explicit population, event timing, currency and completeness policy
- Using SUM without checking the source relationship
- Relying on whichever date field is easiest to type