SQL and Power BI Practice: Reconcile Orders, Refunds and Net Revenue

Use SQL and Power BI to reconcile sales and refunds without double-counting orders. Start with three sample orders and two refunds, check the ₹2,200 net revenue by hand, then test what happens when a refund has no matching order.

Start with three orders and two returns

The expected totals are ₹2,500 gross, ₹300 returned and ₹2,200 net across three orders. Store gross amounts in an orders table with one row per order. Store each refund in a separate returns table, using positive amounts.

Group returns before joining

WITH returns_by_order AS (
  SELECT order_id, SUM(refund_amount) AS refund_total
  FROM returns
  GROUP BY order_id
)
SELECT o.order_id, o.gross_amount,
       COALESCE(r.refund_total, 0) AS refund_total,
       o.gross_amount - COALESCE(r.refund_total, 0) AS net_revenue
FROM orders AS o
LEFT JOIN returns_by_order AS r ON r.order_id = o.order_id;

A direct join to the two refund rows would repeat order 101 and its gross amount. Grouping first keeps one row per order. The left join preserves orders without refunds.

Check the numbers before building a chart

1. Count the result rows. Expect three, with each order ID appearing once.
2. Compare gross and refund totals against the source tables. Check for refunds without matching orders separately.
3. In Power BI, sum the three amount columns and display gross, refunds and net side by side. Filter to order 101 and confirm the three numbers are ₹1,200, ₹300 and ₹900.

How do I find refunds without a matching order?

Run a separate exception query before trusting the dashboard total. A refund with an unknown order ID cannot be assigned to an order, so it is absent from the order-level report. Check whether the order extract is incomplete, the join keys differ, or the source contains a bad ID.

SELECT r.order_id, COUNT(*) AS refund_rows,
       SUM(r.refund_amount) AS unmatched_refund_total
FROM returns AS r
LEFT JOIN orders AS o ON o.order_id = r.order_id
WHERE o.order_id IS NULL
GROUP BY r.order_id;

Add a synthetic ₹50 refund for order 999 to test the check. The exception query should return one row for 999. The matched-order net stays ₹2,200, while total refunds in the source rise to ₹350. Show the ₹50 difference separately until you can explain it.

Which date should a dashboard use for returns?

Use the refund date for a report about refunds processed during a period. Use the original order date when comparing orders by purchase cohort, and state the cutoff for returns included. Those views answer different questions, so label them clearly.

What should I show in my portfolio?

Include the sample data, the query, the expected totals and one screenshot. Explain how grouping before the join prevents double-counting and how you chose the date used in the report.

For a related walkthrough, see the beginner SQL-to-Power-BI dashboard project. Explore the ScholarLogic Data Analytics course for the wider syllabus.

ScholarLogic Editorial Team

ScholarLogic Editorial Team writes IT news and career guides for students and freshers in Bengaluru. We summarise announcements from their official sources, link to those sources, and label our own opinions separately. ScholarLogic teaches IT courses at BTM Layout.