Level 8 · Boss: the inherited KPI pack

Inherited reports rarely have just one bug. This boss gives you three queries from the same pack. Each runs, each returns a tidy table, and each tells the board something false.

Step 1: monthly revenue and shipping

Two bugs from this chapter. The join to order_items repeats every order’s shipping_fee once per line, so shipping is inflated in every month: January shows $1,223.15 instead of $752.17. And BETWEEN '2025-01-01' AND '2025-12-31' stops at midnight on Dec 31, so December has 623 orders instead of 634.

Fix the fan-out first by summing lines per order in a CTE. Then switch to a half-open range. The order count, the revenue and the shipping total should all match the order system afterward.

Step 2: cancellation rate per channel

The starter returns 12 rows for 3 channels. GROUP BY channel, status splits each channel into one row per status, so the cancelled row shows 100% and every other row 0%. That is a grain bug: the extra column makes the grouping finer than the question.

Fix the grouping and a second bug appears: // is integer division, so app’s rate reads 6 instead of 6.23. Use 100.0 * and divide with /.

Step 3: repeat rate per segment

A repeat rate of exactly 100% for both segments is impossible. The HAVING COUNT(*) >= 2 inside the CTE removes one-time buyers before the outer query counts buyers, so numerator and denominator are the same group. This is the WHERE versus HAVING lesson in another form: a filter applied at the wrong step changes what the aggregate means.

Remove the HAVING. The outer FILTER (WHERE b.orders >= 2) already restricts the numerator. Business has 253 buyers and 188 repeat buyers, consumer 1,194 and 690.

How to work through a broken pack

1. For each query, say in one sentence what one output row should be. Compare with the row count.
2. Rebuild one number from the simplest possible query: shipping from orders alone, December’s count with a half-open range, buyers with a plain COUNT(DISTINCT customer_id).
3. Fix one bug, rerun, compare again. A second bug often hides behind the first.

Common mistakes

• Treating COUNT(DISTINCT o.order_id) in step 1 as proof that the query has no fan-out. It protects the count, not the sums.
• Fixing step 2’s division but not its grouping: 0% and 100% rows are still wrong.
• Moving step 3’s HAVING into the outer query: that filters segments, not customers.

In SQL Server

T-SQL has no FILTER clause. Write conditional counts with CASE, which also works in DuckDB:

SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_orders,
ROUND(100.0 * SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) / COUNT(*), 2)

On the job

Write down what was wrong in each query when you hand the pack back. The board will ask why last quarter’s numbers changed, and “shipping was counted once per line” is an answer they can accept.