Patterns

Learn SQL patterns with practical examples and guided drills on dbSyntax.

Common Patterns

Reusable query shapes for frequent analytics problems. Each page shows the canonical form, the pitfalls (non-deterministic ranking, tie-brea...

Pick the best N rows inside each partition. The canonical shape: rank each row within its partition with ROW_NUMBER (or RANK / DENSE_RANK de...

Keep one row per key, typically the most recent. A specific case of top-N-per-group with N = 1. Used constantly against event streams, audit...

Turn the distinct values of a column into separate output columns. DuckDB (and SQL Server, Snowflake) offers a built-in PIVOT statement. Els...

Turn multiple columns into name/value pairs: one row per (key, column_name, column_value). The inverse of PIVOT, it narrows wide tables into...