Logical execution order
SQL is written SELECT-first but evaluated FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. That is why a SELECT alias cannot be used in WHERE, and why HAVING filters groups while WHERE filters rows.
Joins
INNER keeps matching rows; LEFT keeps all rows from the left side; FULL keeps both sides; CROSS produces the cartesian product. A join on a non-unique key multiplies rows — the classic cause of inflated totals in a dashboard.
NULL is not a value
NULL means unknown, so NULL = NULL is unknown, not true. Use IS NULL, and remember COUNT(col) skips NULLs while COUNT(*) does not. COALESCE supplies defaults.
SELECT c.country,
COUNT(DISTINCT o.customer_id) AS buyers,
SUM(o.total_cents) / 100.0 AS revenue
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
AND o.created_at >= DATE '2026-01-01'
WHERE c.status = 'active'
GROUP BY c.country
HAVING SUM(o.total_cents) > 100000
ORDER BY revenue DESC
LIMIT 20;