Incident Lab · SEV2 · SQL
Revenue per customer short after the "duplicate fix"
Billing: customer 101 paid for three orders (218.00) but the account report shows 169.00.
customer revenue joins orders to shipments to count parcels. An order can ship in several parcels, so the join repeats the order's amount once per parcel and revenue came out too high. The quick fix was sum(DISTINCT o.amount) . That removes duplicate values , not duplicate orders . Customer 101 has two separate 49.00 subscription orders, and DISTINCT collapses them into one. Any customer with two orders of the same amount is now under-reported. Investigate: Fix customer revenue so each order's amount counts once, no matter how many parcels it shipped in (and orders with no shipment still count). Columns: customer id , orders , parcels (0 when none), revenue (rounded to 2). Order by customer id .