Bridge · From syntax to engineering · 35 MIN

Project · Report on customers with no orders

A useful report must define what zero activity means.

LEFT JOIN retains customers with no matching orders. COUNT(o.id) counts real order rows, whereas COUNT(*) also counts the unmatched placeholder row. COALESCE turns an absent SUM into zero. Keep refunds as negative amounts so the report reflects net value. Group by both selected customer columns for a portable query. Before adding more one-to-many joins, preaggregate each side: otherwise their combinations can inflate totals. A report is correct only when its row grain is explicit.

Write a small contract first, then test how the implementation behaves at its boundaries.

Read the example

SELECT id, name FROM customers ORDER BY id;

[[1,"Ada"],[2,"Lin"],[3,"Sam"]]

Your challenge

Return customer id, name, order_count, and net_amount for every customer. Include customers without orders with zero counts and totals. Order by customer id.

Solution cost: Depends on joins, grouping and indexes; inspect EXPLAIN QUERY PLAN. time · Up to O(c + o) temporary grouping or join state, engine dependent. space

Common trap

COUNT(*) counts the placeholder produced by LEFT JOIN for a customer with no orders.

Further reading: SQLite SELECT and joins