A join relates rows from two tables. Here one customer can have many orders, and a customer can have none. A primary key identifies a customer independently of their display name. Two customers named Ada must remain two report rows.
An INNER JOIN returns matching pairs. A LEFT JOIN also preserves left rows with no match, supplying NULLs for the right columns. If a customer has two matching orders, the intermediate result has two rows for that customer. This multiplicity is a feature, and aggregation must account for it.
Follow the data
Start from the table whose rows must survive: customers. Match by customer_id and restrict paid status in ON. For a customer with only pending orders, there is no paid match, so LEFT JOIN emits one NULL-extended row. COUNT(o.id) ignores its NULL order id and returns zero. COUNT(*) would count the preserved row and incorrectly return one.
SUM(o.amount) over the missing match is NULL. The report contract wants zero, so COALESCE supplies it. The fallback is a reporting policy rather than proof that a matching order exists. A genuine paid order with amount zero still counts as one order.
A right-side filter in WHERE is applied after the join. WHERE o.status = 'paid' removes NULL-extended rows and loses customers with no paid order. Keep the filter in ON when it defines eligible matches while preserving customers.
Fanout becomes more subtle when joining two one-to-many children. Joining two orders and three support tickets by customer creates six intermediate pairs. Summing order amounts then counts each order three times. Aggregate each child to customer grain first, or use separate correlated aggregates; DISTINCT amounts is not a general repair because two real orders can have equal amounts.
Work a complete example
The example includes its own schema and data. Every Run creates a new SQLite database, so changing or rerunning it cannot change your other exercises.
CREATE TABLE customers(id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id), amount INTEGER NOT NULL, status TEXT NOT NULL);
INSERT INTO customers VALUES (1,'Ada'),(2,'Ada'),(3,'Lin');
INSERT INTO orders VALUES (1,1,20,'paid'),(2,1,20,'paid'),(3,2,99,'pending');
SELECT c.id, c.name, COUNT(o.id) AS paid_orders,
COALESCE(SUM(o.amount), 0) AS paid_total
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;Diagnose the common failure
The same amount in two orders does not make one a duplicate to remove. COUNT(DISTINCT amount) and SUM(DISTINCT amount) measure distinct prices, not order identities. Group by the customer key, not only the name. Both errors can pass a tiny sample unless fixtures include repeated names and equal-priced orders.
Try a change before building
Give the third customer one paid order for amount zero. Predict both aggregates. Then move the paid-status condition to WHERE and explain which customers disappear. Return it to ON before continuing.
Move into practice
Relate tables by keys, preserve unmatched rows and avoid accidental fanout. Checks test the rules; bug hunts ask you to identify a faulty line before seeing its test evidence. The fill-in keeps most of a working statement, and the build asks for the full contract. Public and hidden fixtures vary inputs, preserve duplicate counts and distinguish NULL from text. SQL results appear as tables; predict-output checks use compact JSON rows, one row per line, with null for SQL NULL.
Use the Watch & read resources for the lecture and primary SQLite reference. Reading or watching alone does not grant mastery. These are SQLite exercises; PostgreSQL, MySQL and SQL Server differ in types, syntax, query plans and concurrency behavior.