Level 4 · Shipping and merchandise revenue per channel

Brian’s query is a textbook case of the fan-out from chapter 5. shipping_fee lives on orders, one value per order. After joining to order_items, each order’s fee is copied onto every line, and SUM(o.shipping_fee) adds it once per line. Run the starter: shipping comes out about 1.5 times too high on web and app and about 2.4 times too high on marketplace, where fee-paying orders tend to have more lines. The inflation is not even a constant factor you could divide away. Merchandise revenue is fine, because it is a line-level number summed at line level.

The fix: aggregate the many side first

Bring order_items down to the grain of orders (one row per order) before joining:

WITH line_totals AS (
  SELECT order_id,
         SUM(quantity * unit_price * (1 - discount_pct / 100.0)) AS merchandise
  FROM order_items
  WHERE quantity > 0
  GROUP BY order_id
)
SELECT o.channel,
       COUNT(*) AS orders,
       ROUND(SUM(o.shipping_fee), 2) AS shipping_revenue,
       ROUND(SUM(COALESCE(l.merchandise, 0)), 2) AS merchandise_revenue
FROM orders AS o
LEFT JOIN line_totals AS l ON l.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY o.channel;

Now each order matches at most one line_totals row. The join cannot multiply anything, so both sums are correct and COUNT(*) counts orders again.

Why LEFT JOIN and COALESCE

An order whose lines were all filtered out (for example, only bad quantities) has no line_totals row. An inner join would drop that order and its shipping fee. The LEFT JOIN keeps it, and COALESCE(l.merchandise, 0) treats its missing merchandise as zero. Here zero is a true default: the order had no valid merchandise, so this is the legitimate use of COALESCE from chapter 6, not hiding an unknown.

The general recipe

When you need measures from two tables at different grains:

1. Decide the output grain (here: channel).
2. Aggregate each “many” table up to the join key (here: per order) in its own CTE.
3. Join the CTEs one-to-one, then aggregate to the output grain.

With two child tables, for example lines and returns per order, the recipe matters even more. Joining both raw tables to orders multiplies lines by returns, and every number is wrong. Pre-aggregate each one separately, then join.

Reconcile

The finance check is simple: SELECT SUM(shipping_fee) FROM orders WHERE status = 'completed' must equal the sum of shipping_revenue across channels. If it does, the fan-out is gone. Build this kind of cross-check into your routine for any number that goes to finance.

Interview note

This pattern, “aggregate before you join”, is one of the clearest signals of experience in an analyst interview. If you are asked to combine orders, items and payments or sessions, say up front which grain each table has and how you will avoid double counting. It often matters more than the final syntax.