Level 1 · Shipping revenue that grew after a join

The starter’s revenue column is right and its shipping column is wrong, in the same query. That combination is the signature of join fan-out: a sum over a column that lives on the “one” side of a one-to-many join.

What the query actually does

orders has one row per order and order_items one row per line. After the join, an order with three lines appears three times, and its shipping_fee is copied onto all three rows. Marketplace order 8235 has a $4.99 fee and five lines, so the starter adds $24.95 for it.

Line revenue is fine, because each line’s amount appears exactly once. COUNT(DISTINCT o.order_id) is fine too. Only SUM(o.shipping_fee) repeats.

How to spot it

Compare row counts before you trust a sum. There are 4,295 completed orders in 2025, but the join returns 10,800 rows. If the grain you need is “one row per order” and the join gives 2.5 times that, every sum of an order-level column is inflated.

A second check is to compute the suspicious number without the join:

SELECT channel, ROUND(SUM(shipping_fee), 2) AS shipping_revenue
FROM orders
WHERE status = 'completed'
  AND order_date >= '2025-01-01' AND order_date < '2026-01-01'
GROUP BY channel;

Marketplace comes back at $3,897.76, not $8,919.83. Web is $3,890.50 instead of $5,588.90, and app $4,309.25 instead of $6,532.35.

The fix: aggregate the many side first

Summarize order_items to one row per order in a CTE, then join. The join is one-to-one, so both sums are correct in one query and the order count is a plain COUNT(*).

Common mistakes

• SUM(DISTINCT o.shipping_fee): collapses different orders that share a fee.
• Dividing the inflated sum by the average number of lines: an approximation that changes every time the basket size changes.
• Filtering quantity > 0 in the outer query after pre-aggregating: the CTE already handles it, and the outer query no longer has a quantity column.

In SQL Server

The same CTE works unchanged in T-SQL. One difference shows up when you test pieces: SQL Server has no LIMIT, so look at a few joined rows with TOP:

SELECT TOP 10 o.order_id, o.shipping_fee, i.order_item_id
FROM orders AS o JOIN order_items AS i ON i.order_id = o.order_id;

On the job

“Revenue matches but shipping, tax or discounts at order level do not” almost always means fan-out. Name the grain of each table out loud before you write the SELECT.