Keep missing relationships visible

First, build a mental model

A LEFT JOIN preserves the left-hand roster. Unmatched right-hand values become NULL placeholders; later WHERE conditions can remove those preserved rows again.

1. Put a condition in ON when it defines which right-hand rows match. Put it in WHERE when it should remove rows from the final joined relation.
2. COUNT(*) counts joined rows including a preserved placeholder. COUNT(o.id) counts only non-null order IDs.
3. SUM over no non-null inputs returns NULL. COALESCE can represent zero when the question defines no matching orders as zero; it must not conceal unknown source values.
4. The lab fixture has Mira with a paid order, Noor with only a draft, and Lee with no orders. This makes accidental row loss observable.

SELECT c.id, c.name, COUNT(o.id) AS paid_orders,
       COALESCE(SUM(o.total_cents), 0) AS paid_cents
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id AND o.status = 'paid'
GROUP BY c.id, c.name
ORDER BY c.id;

1. Roster

   Start with three customer rows.

2. Match

   Only paid orders match. Noor and Lee still have a preserved customer row with NULL order columns.

3. Aggregate

   Mira has 1 paid order / 5000 cents; Noor and Lee each have 0 / 0.