EXIN SQL Foundation: A Revenue Report Inflated by Join Fan-Out

A Revenue Report Inflated by Join Fan-Out

A marketplace runs MySQL 8.0. Three tables matter: orders (one row per order), order_items (an average of 3.4 rows per order) and payments (1.2 rows per order, because some orders are settled in two instalments). regions holds 14 regions, two of which were launched last quarter. A BI developer writes:

SELECT r.name,
       SUM(oi.quantity * oi.unit_price) AS item_revenue,
       SUM(p.amount)                    AS paid
FROM orders o
JOIN regions r      ON r.id = o.region_id
JOIN order_items oi ON oi.order_id = o.id
JOIN payments p     ON p.order_id = o.id
GROUP BY r.name;

Finance finds that paid is exactly 3.4 times the ledger total for the same period, item_revenue is about 20% too high, and the two new regions do not appear at all, although they do have orders - their customers are invoiced but settle at the end of the quarter, so no payment rows exist yet. The developer is asked to fix the report without changing the tables and without losing the per-order detail the finance team occasionally drills into.

1. Rewrite the query so that both totals are correct and orders with no payments still appear.

Read the worked answer

Aggregate each fact table first, then join the already-aggregated results. Each subquery returns at most one row per order_id, so joining them cannot multiply anything.

WITH items AS (
  SELECT order_id, SUM(quantity * unit_price) AS item_revenue
  FROM order_items
  GROUP BY order_id
),
pays AS (
  SELECT order_id, SUM(amount) AS paid
  FROM payments
  GROUP BY order_id
)
SELECT r.id,
       r.name,
       COALESCE(SUM(i.item_revenue), 0) AS item_revenue,
       COALESCE(SUM(p.paid), 0)         AS paid
FROM orders o
JOIN regions r     ON r.id = o.region_id
LEFT JOIN items i  ON i.order_id = o.id
LEFT JOIN pays  p  ON p.order_id = o.id
GROUP BY r.id, r.name;

Why each choice.

The CTEs collapse each child table to one row per order - fan-out is now impossible.
LEFT JOIN from orders keeps orders that have no items or no payments yet; an inner join would silently drop them.
COALESCE(...,0) distinguishes 'nothing invoiced or paid' from NULL in the output; finance reads 0, not a blank cell.
Grouping by r.id keeps regions distinct even if two share a name.
The per-order detail finance drills into is not lost: the same CTEs can be exposed directly (SELECT * FROM items JOIN pays USING (order_id)) rather than summed.

If the platform needs a single pass, MySQL 8 also supports correlated scalar subqueries in the select list, but the pre-aggregated CTE version is usually the clearer and better-optimised form.

2. Why do the two new regions vanish from the report, and what is the rule for choosing between WHERE and HAVING when the report is next extended to filter regions and to hide low-volume rows?

Read the worked answer

Why the regions vanish. The query uses inner joins to order_items and payments. An order with no payment rows has no match, so the whole order is eliminated before grouping - and a region whose orders are all unpaid is eliminated entirely. Nothing is wrong with the regions table; the join type is wrong. Replacing both with LEFT JOIN and wrapping the aggregates in COALESCE(..., 0) makes those regions appear with paid = 0.

Diagnostic habit worth keeping: an inner join on a child table is a filter that nobody wrote. Compare row counts before and after each join while building the query.

WHERE versus HAVING.

WHERE filters rows before grouping. It cannot reference aggregates and it removes rows before they contribute to SUM.
HAVING filters groups after aggregation. It can reference aggregates.

SELECT r.id, r.name, SUM(p.amount) AS paid
FROM orders o
JOIN regions r    ON r.id = o.region_id
LEFT JOIN payments p ON p.order_id = o.id
WHERE o.order_date >= '2024-01-01'      -- row filter: correct place
GROUP BY r.id, r.name
HAVING SUM(p.amount) >= 10000;          -- group filter: correct place

Putting o.order_date >= '2024-01-01' in HAVING would still run (MySQL tolerates it), but it filters after the sums are computed, so the totals would include orders from before the period - a result that looks plausible and is wrong. Conversely, HAVING paid >= 10000 cannot be written in WHERE because the alias does not exist at that stage. Filter rows in WHERE, filter groups in HAVING.