The querying spine
18 patterns, ordered from the ground up — each a map of the exact places real queries go wrong, with the one-line truth and a runnable counterexample for every trap.
WHERE semantics: NULL-aware predicates, timestamp ranges, LIKE vs equality, implicit casts, AND/OR precedence.
UNKNOWN vs FALSE; IS [NOT] NULL vs =; COALESCE/NULLIF; NULL propagation; aggregates ignore NULLs.
ORDER BY determinism, ties at the LIMIT boundary, NULL sort position, ordinal vs expression.
COUNT variants, SUM over empty/all-NULL, WHERE vs HAVING, non-aggregated columns, NULL groups.
LEFT-turned-INNER, NULL keys, anti-join three ways, ON vs WHERE in outer joins, self-join.
One-to-many multiplication before aggregation; detecting fan-out; many-to-many; grain as a declared property.
UNION dedups vs UNION ALL; position-based column alignment; NULL equality for dedup; EXCEPT/INTERSECT.
Scalar cardinality, correlated vs uncorrelated, EXISTS vs IN, CTE fencing, recursive CTEs.
Conditional aggregation, CASE evaluation order, CASE in GROUP/ORDER BY, FILTER clause.
ROW_NUMBER/RANK/DENSE_RANK on ties, PARTITION vs GROUP, top-N per group, LAG/LEAD.
ROWS vs RANGE, default-frame peer ties, rolling windows over gapped dates, moving-average edges.
Half-open intervals, truncate vs extract, timezones/DST, date spines, week/month boundaries.
Deterministic latest via complete ORDER BY, DISTINCT vs GROUP BY vs QUALIFY, dedup before/after join.
row_number differencing, gap-to-session boundaries, session metrics, off-by-one at boundaries.
Conditional-aggregation pivot, UNION-ALL/lateral unpivot, wide-vs-long as a modeling choice, dynamic-column limits.
Duplicate-source grain, idempotent upsert, DELETE/UPDATE with joins, INSERT-SELECT position risk, transaction batches.
Sargability, plan smells (scan/join strategy), SELECT* cost on columnar, spill, row-goal effects.
JSON extraction, LATERAL/FLATTEN, QUALIFY idiom, array contains/overlap predicates.