SQL Patterns
Fifteen reusable query templates that show up in every SQL interview — aggregation, filtering, comparison, sequencing/hierarchical, transformation. The decomposition vocabulary for data problems.
The SQL Patterns topic is to DBMS what Design Patterns is to LLD: a small set of repeatable query shapes that, once internalised, let you decompose any data problem into known moves. Where the SQL topic teaches the language (verbs, joins, window functions), this topic teaches the *problem-solving shapes* — Tally Count for grouped frequency, Rolling Totals for cumulative time-series, Patch Gaps for missing-data inference, Gaps and Islands for streak detection, Pivot Flip for wide-vs-long reshapes.
The patterns group into five families: aggregation, filtering, comparison, sequencing/hierarchical, and transformation. In an interview the first move is to recognise which family the problem belongs to; the second move is to write the pattern's template; the third is to adapt it to the schema in front of you. Most SQL interview problems decompose into one or two patterns. Once you've seen 15, you've seen most of them.
Key concepts
- Recognise the family first (aggregation / filtering / comparison / sequencing / transformation) before reaching for syntax
- Most problems decompose into one or two patterns — name them out loud before writing SQL
- Window functions unlock the sequencing and ranking patterns; without them you write subquery soup
- Recursive CTEs are the escape hatch for tree-shaped problems — but cost more than people expect
- Conditional aggregation (CASE inside SUM) is the portable Pivot — use it when the engine lacks a native PIVOT
- EXISTS beats IN for nullable subqueries; LEFT JOIN IS NULL beats both for large outer sets — pick by data shape
Reference template
// Recognising a pattern in 30 seconds
1. What's the input shape? (single table? joined? hierarchical?)
2. What's the output shape? (one row per group? running total? wide?)
3. Which family is the move? (aggregation / filter / compare / sequence / transform)
4. Which template fits? (the 15 in this topic cover ~90% of interviews)
5. Adapt to the schema (table names, column names, types)
6. Watch the engine differences (PIVOT, DISTINCT ON, ILIKE, recursive depth)Adapt to your problem; the structure is the load-bearing part.
Common pitfalls
- Reaching for a recursive CTE when a self-join would do — readability and performance both suffer
- IN with nullable subquery results — silently returns nothing; EXISTS is safer
- Forgetting ROWS BETWEEN UNBOUNDED PRECEDING in rolling-totals — get a partition-default window instead of true cumulative
- Calling ROW_NUMBER when you needed RANK (or vice versa) — the tie-breaking difference matters more than people remember
- Pivoting in the application layer when the database has PIVOT / crosstab / conditional aggregation built in
- Pattern-matching with LIKE when the column has a functional index that requires regex or trigram instead
Related topics
Items (15)
- Tally Count
Count occurrences of values in a column. The COUNT(*) GROUP BY template every SQL interview opens with.
Pattern Foundational - Group Bucket
Bucket continuous values into ranges (age bands, price tiers) using CASE WHEN inside GROUP BY.
Pattern Foundational - Rolling Totals
Cumulative aggregations over a window. SUM() OVER (ORDER BY ... ROWS BETWEEN ...). The time-series running-total template.
Pattern Intermediate - Patch Gaps
Fill missing rows in a sequence with default values via LEFT JOIN against a generated calendar or series.
Pattern Intermediate - Existence Check
EXISTS vs NOT EXISTS vs IN vs LEFT JOIN IS NULL — four ways to say where this related row does or does not exist.
Pattern Foundational - Deduplication
ROW_NUMBER() OVER (PARTITION BY ...) = 1 and the DISTINCT ON (Postgres) alternatives for keeping one row per group.
Pattern Foundational - Pattern Search
LIKE, ILIKE, regex, trigram, full-text — the spectrum of find rows that match a string pattern.
Pattern Foundational - Set Compare
INTERSECT / EXCEPT / UNION and the equivalent JOIN / NOT EXISTS forms. When set algebra beats joins.
Pattern Intermediate - Join Variants
Self-join, anti-join, semi-join, lateral join, theta-join. Beyond the standard four kinds of join.
Pattern Intermediate - Nested Query
Correlated vs uncorrelated subqueries; subquery in SELECT vs WHERE vs FROM. When CTEs beat nested subqueries.
Pattern Intermediate - Rank and Row
ROW_NUMBER vs RANK vs DENSE_RANK. Per-partition top-N selection. The second-highest-salary canon.
Pattern Intermediate - Lead-Lag Navigation
LEAD() and LAG() for previous/next-row comparison. Streak detection, change-from-previous metrics, run-length tracking.
Pattern Intermediate - Recursive Tree
Recursive CTEs (WITH RECURSIVE) for tree traversal, transitive closures, organisational hierarchies.
Pattern Advanced - Gaps and Islands
The streak-detection template: subtract row number from a value, group by the difference. Identifies runs of consecutive rows.
Pattern Advanced - Pivot Flip
Long-to-wide and wide-to-long reshapes. PIVOT (T-SQL), crosstab (Postgres), conditional aggregation as the portable form.
Pattern Intermediate