Level 5 · Debug: refunds that went missing

This query runs, returns twelve plausible months and is wrong by more than $12,000. The bug is one word.

What went wrong

UNION stacks the two sources and then removes duplicate rows. The CTE only keeps two columns, month and amount, so any two refunds in the same month with the same amount look identical, and one of them disappears. In December 2025 alone there are six separate returns refunded at $20.99, four at $15.99 and four at $159.00. After UNION, each of those groups counts once.

The numbers:

	rows in refund_lines	2025 refunds
UNION (starter)	567	$135,621.44
UNION ALL (fix)	695	$147,817.72

695 is exactly 659 returns in 2025 plus 36 refunded orders with a shipping fee. 567 matches nothing, and that mismatch is how you catch the bug.

How to spot it

The reliable check for any stacked query is a row count reconciliation: count each source on its own, add them up and compare with the count of the stacked result.

SELECT COUNT(*) FROM returns
WHERE returned_on >= '2025-01-01' AND returned_on < '2026-01-01';   -- 659

If the stack has fewer rows than the sum of its parts, something removed rows, and plain UNION is the first suspect. The second suspect is a branch with a filter you did not intend.

Another tell: the starter would be “right” if every refund had a unique amount. The bug grows with volume, so it is worst in December, the month with the most returns, which makes the seasonal story in the report look calmer than it was.

The fix

Change UNION to UNION ALL. Nothing else changes: same columns, same filters, same aggregation. If you genuinely needed to remove duplicates (for example the same refund exported twice), do it on a real key such as return_id, not on whatever columns the query happens to select.

Common mistakes

• Adding DISTINCT to “clean up” a stack of amounts. Same bug, different keyword.
• Fixing it by adding a random column like RANDOM() so rows differ. Use UNION ALL and keep ids if you need to trace rows.
• Reconciling only the total. Compare row counts too: totals can match by accident, counts rarely do.

In SQL Server

Identical behaviour: UNION deduplicates, UNION ALL does not. The month start would be DATEFROMPARTS(YEAR(returned_on), MONTH(returned_on), 1). In the execution plan, a plain UNION shows up as an extra Distinct Sort or Hash Match step, which is a quick way to notice it in someone else’s query.