Raw transactional tables rarely arrive at the grain a dashboard needs. An order can have several lines and several refunds. Joining both raw children to the order creates all line/refund combinations within that order.
In the sample, order 10 has two lines totaling 3,500 cents and two refunds totaling 750 cents. Its correct net is 2,750 cents. The raw join has four rows and repeats both sets of amounts. The apparent detail makes a wrong total look convincing.
Build a line_totals CTE grouped by order_id and a refund_totals CTE grouped separately by order_id. Join both to orders, not to one another’s raw records. The aggregate keys are the guarantee: each joined side has at most one row per order.
Retain quantity and the price recorded at sale time. A current product price would rewrite history. Refund adjustments here belong to the original order’s business date; a cash-flow report assigned to refund dates would need a different fact definition.
WITH lines(order_id,qty,price) AS (VALUES(1,2,1000),(1,1,1500)),
refunds(order_id,amount) AS (VALUES(1,500),(1,250)),
l AS (SELECT order_id,SUM(qty*price) AS gross FROM lines GROUP BY order_id),
r AS (SELECT order_id,SUM(amount) AS refund FROM refunds GROUP BY order_id)
SELECT l.order_id,l.gross-r.refund AS net FROM l JOIN r USING(order_id);SUM(DISTINCT money) does not restore record identity. Two legitimate lines can cost the same amount. Deduplicating by value removes a real contribution. Likewise, COUNT(DISTINCT order_id) may fix a count while leaving multiplied money sums wrong.
Use LEFT JOIN for child totals and customer dimensions. No refund is common; no line is a deliberate data-quality fixture. Neither absence silently changes this lesson’s completed-order denominator. Coalesce absent child totals to zero and absent dimensions to their explicit labels.
This pattern transfers to BI modeling: know a fact table’s grain and relationship cardinality before choosing measures. A star schema is a useful modeling pattern, not a substitute for checking keys and counts.
WITH orders(id) AS (VALUES(1),(2)),
refunds(order_id,amount) AS (VALUES(1,300)),
r AS (SELECT order_id,SUM(amount) AS total FROM refunds GROUP BY order_id)
SELECT o.id,COALESCE(r.total,0) AS refund_cents
FROM orders AS o LEFT JOIN r ON r.order_id=o.id ORDER BY o.id;Implement the raw-table query. Inspect multi-line/multi-refund, quantity greater than one, guest, no-refund and no-line cases. Compare the accepted rows to lesson 1 before drawing any chart.
Next we turn those reconciled facts into categorical visuals with explicit axes.