SQL Query Rewriting Patterns: 20 Rewrites Every Senior Engineer Knows

sql query rewriting patterns is the primitive every senior data engineer, analytics engineer, and DBA reaches for when a query that "should be fast" is 100× slower than it needs to be, and the optimiser can't fix the plan without a hint from the query author. Every engineer knows SELECT and JOIN on day one; knowing why a correlated subquery in the WHERE clause is often O(N × M), why NOT IN (subquery with NULL) returns zero rows, why DISTINCT over a big table is slower than GROUP BY, and when to use WITH ... AS MATERIALIZED to force a CTE fence is what separates a senior SQL candidate from everyone else. This guide is the catalog of 20 canonical rewrites every senior engineer keeps in their toolkit.

The tour walks the five core rewrite families — (1) correlated subqueries into JOINs (often 10-100× faster because the planner can hash-join instead of nested-loop per outer row), (2) EXISTS / IN / JOIN semantic equivalences and their NOT variants with NULL traps, (3) DISTINCT alternatives (GROUP BY, DISTINCT ON, ROW_NUMBER() = 1 for top-N-per-group), (4) UNION vs UNION ALL cost and semantics, and (5) window function vs GROUP BY vs self-join rewrites, plus CTE materialisation control on Postgres 12+ where CTEs inline by default. Every section ships a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown — so you leave with the mental model and the SQL to ship the same fix into your codebase tomorrow morning.

When you want hands-on reps immediately after reading, drill the SQL optimization practice library →, sharpen the general SQL practice surface →, and layer SQL join drills → since most rewrites involve turning something else into a join.

---

On this page

• Why query rewriting matters in 2026
• Correlated subqueries → JOINs
• EXISTS vs IN vs JOIN + NOT patterns
• DISTINCT alternatives + UNION vs UNION ALL
• WINDOW vs GROUP BY + CTE materialisation + dialect matrix
• Cheat sheet — 20 rewrites recipe list
• Frequently asked questions
• Practice on PipeCode

---

1. Why query rewriting matters in 2026

The sql query rewriting patterns mental model — why the same logical result can execute as O(N) or O(N × M) depending on how you write the SQL

The one-sentence invariant: modern SQL optimisers are impressive but not omniscient — they can rewrite some patterns automatically (e.g., subquery unnesting) but leave others alone (e.g., DISTINCT over a large table), so every senior engineer keeps a catalog of "how do I express this differently to get a better plan" in their head; the highest-leverage rewrites are the 20 patterns in this guide.

Where query rewriting shows up.

• On-call — dashboard slow query. 40-second query needs to be 400 ms. Rewrite unlocks the plan.
• Code review. Junior wrote a correlated subquery; senior rewrites to JOIN.
• ETL job time. dbt model running 15 min needs to hit SLA. Rewrite pushes to 90 s.
• Interview. Senior interviewer shows a slow query; asks the candidate to rewrite.
• Legacy migration. Oracle-to-Postgres port; different optimiser preferences require rewrite.

The optimiser's blind spots.

• Correlated subquery evaluation cost. Some optimisers unnest to semi-join; others don't. Rewrite manually.
• DISTINCT on big tables. Costs sort + dedup. GROUP BY may be cheaper.
• NOT IN with NULL. Semantic difference from NOT EXISTS. Optimiser cannot fix.
• CTE materialisation. Postgres 12+ inlines by default; you may want to force materialisation.
• Predicate pushdown. Optimiser might not push filter into subquery for complex queries.

What senior interviewers actually probe.

• Do you know when to rewrite correlated to JOIN? Usually always. Except for tiny subqueries.
• Do you know NOT IN vs NOT EXISTS NULL semantics? NOT IN with NULL = 0 rows.
• Do you know DISTINCT alternatives? GROUP BY, DISTINCT ON, ROW_NUMBER.
• Do you know when to use UNION vs UNION ALL? UNION ALL almost always.
• Do you know when to use window vs GROUP BY? Window preserves rows; GROUP BY collapses.
• Do you know CTE inline vs materialize? Postgres 12+ inlines; force with AS MATERIALIZED.

The 20 rewrites at a glance.

1. Correlated WHERE EXISTS → INNER JOIN.
2. Correlated scalar subquery → LEFT JOIN + aggregate.
3. IN (subquery) → JOIN.
4. NOT IN → NOT EXISTS.
5. NOT IN → LEFT JOIN + IS NULL (anti-join).
6. DISTINCT → GROUP BY.
7. DISTINCT ON (Postgres) for "one row per group".
8. ROW_NUMBER() = 1 for "one row per group with tie-break".
9. UNION → UNION ALL (when no dedup needed).
10. SELECT MAX(col) per group → ROW_NUMBER OVER PARTITION.
11. Self-JOIN → window function for adjacent rows.
12. WHERE col IN (a, b, c) → WHERE col = ANY(ARRAY[a,b,c]).
13. WITH cte AS (...) → WITH cte AS MATERIALIZED (...) when needed.
14. Multi-JOIN with big intermediate → subquery per join.
15. WHERE EXTRACT(...) → range predicate for sargability.
16. ORDER BY RANDOM() → TABLESAMPLE for large samples.
17. SELECT COUNT(*) estimate via pg_class.reltuples.
18. Aggregation of NULLable → COALESCE(SUM(x), 0).
19. Subquery in SELECT → LATERAL JOIN.
20. Nested subqueries → CTE chain for readability.

Worked example — the correlated subquery bottleneck

Code.

-- Slow: correlated subquery in SELECT
SELECT
  o.id,
  (SELECT SUM(i.qty) FROM items i WHERE i.order_id = o.id) AS total_items
FROM orders o;
-- For each o.id, run subquery. O(N * avg_M).

-- Fast: LEFT JOIN + GROUP BY
SELECT o.id, COALESCE(SUM(i.qty), 0) AS total_items
FROM orders o
LEFT JOIN items i ON i.order_id = o.id
GROUP BY o.id;
-- Single pipeline. O(N + M).

Step-by-step explanation.

1. Correlated form — for each order, execute the subquery — worst case nested-loop, O(N × M).
2. LEFT JOIN — hash join builds hash on items.order_id (say, K distinct), probes with orders — O(N + M).
3. On a 1M orders × 10M items table, correlated might take 30 s; JOIN takes 200 ms.
4. Some optimisers unnest correlated subqueries automatically; some don't.
5. Rewrite manually is safer.

Output. Same rows; 100× faster.

Rule of thumb. Always rewrite correlated subqueries to JOIN + GROUP BY unless the subquery is tiny.

sql query rewriting patterns interview question on choosing rewrites

A senior interviewer asks: "Given a slow query, walk me through your rewrite decision tree."

Solution Using the 5-question rewrite checklist

Q1: Is there a correlated subquery?
    Yes → Rewrite to JOIN + GROUP BY.

Q2: Is there NOT IN?
    Yes → Rewrite to NOT EXISTS.

Q3: Is there DISTINCT on a large table?
    Yes → Consider GROUP BY, DISTINCT ON, or ROW_NUMBER.

Q4: Is there UNION where UNION ALL would work?
    Yes → Use UNION ALL.

Q5: Is there a filter with wrapped function (EXTRACT, DATE)?
    Yes → Rewrite as range predicate for sargability.

Output: Ordered checklist for query rewrite.

Why this works — targeted rewrites for the highest-leverage patterns; systematic search order.

SQL
Topic — optimization
SQL optimization drills

Practice →

---

2. Correlated subqueries → JOINs

correlated subquery to join — the highest-leverage rewrite pattern

The mental model in one line: correlated subqueries (subqueries that reference outer-query columns) are convenient to write but potentially expensive to execute — each outer row triggers subquery execution unless the optimiser unnests to a semi-join or aggregate join; rewriting to explicit JOIN + GROUP BY (or JOIN + window) gives you a plan you can predict.

Slot 1 — correlated in WHERE.

• Slow. SELECT * FROM o WHERE EXISTS (SELECT 1 FROM i WHERE i.order_id = o.id).
• Fast. SELECT DISTINCT o.* FROM o JOIN i ON i.order_id = o.id. Or semi-join: SELECT o.* FROM o WHERE o.id IN (SELECT order_id FROM i).
• Note. Modern Postgres unnests EXISTS to semi-join automatically. Some older engines don't.

Slot 2 — correlated in SELECT (scalar subquery).

• Slow. SELECT o.id, (SELECT SUM(qty) FROM i WHERE i.order_id = o.id) AS total FROM o.
• Fast. SELECT o.id, COALESCE(SUM(i.qty), 0) FROM o LEFT JOIN i ON i.order_id = o.id GROUP BY o.id.
• Rewrite — for each aggregate you want per outer row, use one LEFT JOIN + GROUP BY.

Slot 3 — correlated with COUNT.

• Slow. SELECT o.id, (SELECT COUNT(*) FROM i WHERE i.order_id = o.id) AS item_count FROM o.
• Fast. SELECT o.id, COUNT(i.id) AS item_count FROM o LEFT JOIN i ON i.order_id = o.id GROUP BY o.id.

Slot 4 — correlated with EXISTS + additional predicate.

• Slow. SELECT * FROM o WHERE EXISTS (SELECT 1 FROM i WHERE i.order_id = o.id AND i.status = 'shipped').
• Fast — semi-join with predicate. SELECT o.* FROM o WHERE o.id IN (SELECT DISTINCT order_id FROM i WHERE status = 'shipped'). Or INNER JOIN + DISTINCT.

Slot 5 — correlated LATERAL.

• Sometimes correct. SELECT o.id, x.top_item FROM o LEFT JOIN LATERAL (SELECT * FROM i WHERE i.order_id = o.id ORDER BY qty DESC LIMIT 1) x ON TRUE.
• Best when. You need per-outer-row top-N.
• Alternative. ROW_NUMBER OVER (PARTITION BY order_id ORDER BY qty DESC) = 1.

Slot 6 — anti-correlated NOT EXISTS.

• Correct. SELECT * FROM o WHERE NOT EXISTS (SELECT 1 FROM i WHERE i.order_id = o.id).
• Alternative. LEFT JOIN i ... WHERE i.order_id IS NULL. Anti-join.
• NOT IN alternative unsafe. NULL trap.

Slot 7 — cost comparison.

• Nested loop for correlation. O(N × M).
• Hash join after unnest. O(N + M).
• Sort-merge join. O((N + M) log (N + M)).
• Rewrite to JOIN gives the optimiser choice; correlated forces nested-loop unless unnesting is supported.

Slot 8 — when NOT to rewrite.

• Subquery over tiny dim table. Correlated may be simpler and the cost is trivial.
• Optimiser already unnesting. Check EXPLAIN; if plan is already hash semi-join, rewrite is cosmetic.
• Readability. Sometimes correlated form is clearer for reviewers.

Slot 9 — the LATERAL alternative.

• CROSS JOIN LATERAL — for each outer row, correlate to subquery. Same conceptual model.
• When useful. Per-outer top-N via LIMIT inside LATERAL.

Slot 10 — testing rewrites.

• Verify same row count. Count rows before and after.
• Verify same aggregates. Sum, count, distinct check.
• EXPLAIN diff. Compare plan shape.

Worked example — total items per order

Code.

-- Before
SELECT o.id, (SELECT COALESCE(SUM(qty), 0) FROM items WHERE order_id = o.id) AS total
FROM orders o;

-- After
SELECT o.id, COALESCE(SUM(i.qty), 0) AS total
FROM orders o
LEFT JOIN items i ON i.order_id = o.id
GROUP BY o.id;

Output. Same rows, hash-join plan.

Rule of thumb. LEFT JOIN + GROUP BY replaces per-row correlated aggregate.

Worked example — filter by existence

Code.

-- Before
SELECT * FROM users WHERE EXISTS (SELECT 1 FROM sessions WHERE user_id = users.id AND started > NOW() - INTERVAL '7 days');

-- After
SELECT DISTINCT u.* FROM users u
JOIN sessions s ON s.user_id = u.id
WHERE s.started > NOW() - INTERVAL '7 days';

Output. Same users; hash-join plan.

Rule of thumb. EXISTS with predicate → JOIN + WHERE + DISTINCT.

Worked example — per-order top item

Code.

-- LATERAL for top-1 per outer
SELECT o.id, x.item_id, x.qty
FROM orders o
LEFT JOIN LATERAL (
  SELECT item_id, qty FROM items WHERE order_id = o.id ORDER BY qty DESC LIMIT 1
) x ON TRUE;

-- Window function alternative
SELECT id, item_id, qty
FROM (
  SELECT o.id, i.item_id, i.qty,
         ROW_NUMBER() OVER (PARTITION BY o.id ORDER BY i.qty DESC) AS rn
  FROM orders o LEFT JOIN items i ON i.order_id = o.id
) t
WHERE rn = 1;

Output. Same rows.

Rule of thumb. LATERAL is convenient for per-row top-N; window function is portable.

correlated subquery to join interview question

A senior interviewer asks: "Given this correlated subquery, rewrite to hash-join."

Solution Using LEFT JOIN + GROUP BY + COALESCE

-- Rewrite pattern applied
SELECT
  c.id,
  c.name,
  COALESCE(agg.order_count, 0) AS order_count,
  COALESCE(agg.total_revenue, 0) AS total_revenue,
  COALESCE(agg.last_order, NULL) AS last_order
FROM customers c
LEFT JOIN (
  SELECT customer_id,
         COUNT(*) AS order_count,
         SUM(total) AS total_revenue,
         MAX(created_at) AS last_order
  FROM orders
  GROUP BY customer_id
) agg ON agg.customer_id = c.id;

Why this works — one subquery pre-aggregates; JOIN happens once. Cost = O(orders + customers).

SQL
Topic — joins
SQL join drills

Practice →

---

3. EXISTS vs IN vs JOIN + NOT patterns

exists vs in — when each is right, and why NOT IN with NULL returns zero rows

The mental model in one line: EXISTS, IN, and INNER JOIN + DISTINCT are semantically equivalent for the "at-least-one match" pattern; their NEGATIONS NOT EXISTS, NOT IN, and LEFT JOIN + IS NULL are **not semantically equivalent because NOT IN fails with NULL in the subquery; the safe default is NOT EXISTS for anti-joins**.

Slot 1 — EXISTS.

• Semantic. Returns TRUE if subquery returns at least one row.
• Correlated by nature. References outer row.
• Short-circuits. Once a match found, subquery stops.
• Optimiser handles. Usually unnests to semi-join.
• Best. For existence checks.

Slot 2 — IN.

• Semantic. Returns TRUE if outer value is in subquery result.
• Not correlated (subquery is standalone).
• Materialises subquery result. Then hash-joins.
• NULL handling. x IN (NULL, ...) — non-matching x returns UNKNOWN.
• Best. For value membership.

Slot 3 — INNER JOIN + DISTINCT.

• Semantic. Same as EXISTS if you dedup.
• Cost. Sort + dedup for DISTINCT.
• Not always same output. JOIN duplicates if multiple matches in right.
• Best. When you need related columns.

Slot 4 — NOT EXISTS.

• Semantic. Return outer rows where subquery is empty.
• NULL-safe. Handles NULL correctly.
• Best. For anti-joins.

Slot 5 — NOT IN.

• Semantic. Return outer rows where value not in subquery.
• NULL TRAP. x NOT IN (subquery containing NULL) returns zero rows.
• Only use when subquery is guaranteed NULL-free.
• Prefer NOT EXISTS.

Slot 6 — LEFT JOIN + IS NULL.

• Anti-join pattern. LEFT JOIN, filter where right side NULL.
• Semantic. Same as NOT EXISTS.
• Choose column carefully. Use non-NULL column on right side for IS NULL check.

Slot 7 — the decision matrix.

Pattern	NULL safe	Correlated	Best for
EXISTS	✅	✅	Existence check
IN	⚠️ (returns UNKNOWN on NULL)	❌	Value membership
JOIN + DISTINCT	✅	❌	Need related columns
NOT EXISTS	✅	✅	Anti-join (safe)
NOT IN	❌ (0 rows w/ NULL)	❌	Only NULL-free lists
LEFT JOIN + IS NULL	✅	❌	Anti-join

Slot 8 — the NOT IN trap in detail.

-- Suspended customers table has 5 rows, 1 with NULL customer_id
SELECT * FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM suspended_customers);
-- 0 rows (surprise)

Semantics: x NOT IN (a, b, NULL) = NOT (x = a OR x = b OR x = NULL) = NOT (... OR UNKNOWN) = NOT (UNKNOWN or TRUE). If any equality is UNKNOWN and none TRUE, result is UNKNOWN. WHERE drops UNKNOWN.

Slot 9 — NULL-safe NOT IN alternatives.

-- Fix 1: filter NULL from subquery
NOT IN (SELECT c_id FROM sub WHERE c_id IS NOT NULL);

-- Fix 2: NOT EXISTS (always safe)
NOT EXISTS (SELECT 1 FROM sub WHERE sub.c_id = outer.c_id);

Slot 10 — SQL Server / Oracle differences.

• Oracle — behaves similarly; NOT IN with NULL returns 0.
• SQL Server — same.
• All engines consistent on NOT IN semantics.

Worked example — the NOT IN bug

-- Data
outer: 1, 2, 3
sub:   2, NULL

-- Buggy
SELECT * FROM outer WHERE x NOT IN (SELECT y FROM sub);
-- returns 0 rows

-- Fix
SELECT * FROM outer WHERE NOT EXISTS (SELECT 1 FROM sub WHERE sub.y = outer.x);
-- returns: 1, 3

Rule of thumb. Always prefer NOT EXISTS. Never trust NOT IN with subqueries.

exists vs in interview question on NULL-safe anti-join

A senior interviewer asks: "Find orders whose customer is not in the suspended list. Data quality: suspended list may have NULL customer_id."

Solution Using NOT EXISTS with explicit outer-row match

SELECT o.*
FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM suspended_customers s
  WHERE s.customer_id = o.customer_id
);

-- Alternative using LEFT JOIN
SELECT o.*
FROM orders o
LEFT JOIN suspended_customers s ON s.customer_id = o.customer_id
WHERE s.customer_id IS NULL;

Why this works — NOT EXISTS handles NULL correctly. LEFT JOIN + IS NULL alternative equivalent.

SQL
Topic — SQL
SQL practice library

Practice →

---

4. DISTINCT alternatives + UNION vs UNION ALL

distinct alternative — GROUP BY, DISTINCT ON, and ROW_NUMBER for deduplication; UNION vs UNION ALL cost

The mental model in one line: DISTINCT is convenient but forces a sort+dedup step that can dominate cost on large tables; equivalent alternatives — GROUP BY, Postgres's DISTINCT ON, and ROW_NUMBER() OVER (PARTITION BY key ORDER BY tiebreak) = 1 — often produce better plans; UNION similarly sorts+dedups where UNION ALL just concatenates, and you should default to UNION ALL unless duplicates matter.

Slot 1 — DISTINCT.

• Semantic. Remove duplicates from result set.
• Cost. Sort + dedup, or hash-dedup.
• Big table. Expensive.
• When justified. When output is small and dedup is essential.

Slot 2 — DISTINCT → GROUP BY rewrite.

-- Before
SELECT DISTINCT customer_id, region FROM orders;

-- After
SELECT customer_id, region FROM orders GROUP BY customer_id, region;

• Same result. Same cost most of the time. GROUP BY is sometimes optimised better.

Slot 3 — DISTINCT ON (Postgres).

-- Postgres — one row per customer, latest
SELECT DISTINCT ON (customer_id) *
FROM orders
ORDER BY customer_id, created_at DESC;

• Postgres-specific. Very efficient for "top-N per group" with N=1.
• Requires ORDER BY starting with the DISTINCT ON column.

Slot 4 — ROW_NUMBER for top-N per group.

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders
) WHERE rn = 1;

• Portable. Works everywhere.
• More expressive than DISTINCT ON.

Slot 5 — UNION vs UNION ALL.

• UNION. Combines results, removes duplicates. Sort + dedup.
• UNION ALL. Combines results, keeps duplicates. Just concatenates.
• When UNION is right. When duplicates would appear naturally between branches and you must dedupe.
• When UNION ALL is right. When branches naturally disjoint OR duplicates are acceptable.

Slot 6 — UNION cost.

• Sort + dedup. For each concatenated row, must compare against others.
• Hash-based dedup. Better; still requires the hash table.
• UNION ALL cost. O(N + M) — just streams both sides.

Slot 7 — pitfalls.

• Default to UNION when UNION ALL suffices. Silent perf loss.
• UNION ALL doesn't check schema. Schema mismatch is caller error, not caught.
• UNION picks single order. UNION ALL preserves per-branch order.

Slot 8 — DISTINCT + JOIN interaction.

• DISTINCT on JOIN result. Can be expensive because JOIN inflates rows.
• Alternative. Filter to unique input first, then JOIN.
• Example. SELECT DISTINCT o.customer_id FROM orders o JOIN customers c ON c.id = o.customer_id — DISTINCT dedup after JOIN.
• Better. SELECT id FROM customers WHERE id IN (SELECT customer_id FROM orders).

Slot 9 — COUNT(DISTINCT) tricks.

• COUNT(DISTINCT x) — expensive on big data. Requires dedup.
• HyperLogLog. BigQuery APPROX_COUNT_DISTINCT(); Postgres has extensions.
• Trade off exactness for cost.

Slot 10 — semi-join instead of JOIN + DISTINCT.

-- Cost overhead: JOIN + DISTINCT
SELECT DISTINCT c.* FROM customers c JOIN orders o ON o.customer_id = c.id;

-- Better: semi-join
SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Semi-join stops at first match; no dedup needed.

Worked example — DISTINCT vs GROUP BY vs DISTINCT ON

-- Top-1 order per customer

-- Wrong: DISTINCT doesn't preserve column choice
SELECT DISTINCT customer_id, MAX(created_at) FROM orders GROUP BY customer_id;

-- DISTINCT ON (Postgres)
SELECT DISTINCT ON (customer_id) * FROM orders ORDER BY customer_id, created_at DESC;

-- ROW_NUMBER (portable)
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) rn
  FROM orders
) WHERE rn = 1;

Rule of thumb. DISTINCT ON on Postgres; ROW_NUMBER elsewhere for portability.

Worked example — UNION vs UNION ALL

-- Merge active and archive tables
SELECT * FROM active_orders
UNION ALL      -- both are disjoint; no dedup needed
SELECT * FROM archive_orders;

Rule of thumb. Default UNION ALL. Only UNION when duplicates possible AND unwanted.

distinct alternative interview question on top-N per group

A senior interviewer asks: "Get the 3 most recent orders per customer."

Solution Using ROW_NUMBER window function

SELECT customer_id, id, total, created_at
FROM (
  SELECT customer_id, id, total, created_at,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders
) WHERE rn <= 3
ORDER BY customer_id, rn;

Why this works — ROW_NUMBER per-customer ordering; filter rn <= N.

SQL
Topic — window functions
Window function drills

Practice →

SQL Topic — aggregation SQL aggregation drills

Practice →

---

5. WINDOW vs GROUP BY + CTE materialisation + dialect matrix

Window functions vs GROUP BY, CTE materialisation control, and cross-engine rewrite portability

The mental model in one line: window functions preserve every input row while adding computed columns (running total, rank, lag); GROUP BY collapses rows into groups; for "top N per group", "running total", "rank within group", "lag / lead" — always window; for "one row per group with aggregate", either works but window is often more expressive.

Slot 1 — WINDOW basics.

• Preserves rows. Same number in as out.
• Adds computed column. Rank, sum, count, lag.
• OVER (PARTITION BY ... ORDER BY ...) — window definition.

Slot 2 — WINDOW vs GROUP BY.

• GROUP BY collapses. N rows → K groups.
• Window preserves. N rows in, N rows out plus new column.
• When to use each. Window for per-row + summary; GROUP BY for aggregation only.

Slot 3 — CTE inline vs materialise (Postgres 12+).

-- CTE — inlined (planner may merge)
WITH agg AS (SELECT customer_id, SUM(total) FROM orders GROUP BY customer_id)
SELECT * FROM agg WHERE customer_id = 42;
-- Postgres 12+: planner may push predicate down.

-- Force materialisation
WITH agg AS MATERIALIZED (SELECT customer_id, SUM(total) FROM orders GROUP BY customer_id)
SELECT * FROM agg WHERE customer_id = 42;
-- Now agg is computed once, then filtered.

Slot 4 — when MATERIALIZED helps.

• Reuse across multiple SELECTs. If CTE used more than once, MATERIALIZED prevents recompute.
• Large intermediate. Materialise to bound cost.
• Complex plan. Force optimiser to compute in specific order.

Slot 5 — when NOT MATERIALIZED helps.

• Simple CTE. Postgres 12+ inlines; predicate pushdown gives better plan.
• Small CTE, filtered downstream. Inlining lets filter push in.

Slot 6 — CTE alternatives.

• Subquery. Same as inlined CTE.
• Temp table. Same as MATERIALIZED but persists across statements.
• View. Similar to CTE; may materialise.

Slot 7 — LATERAL for per-row logic.

• FROM outer, LATERAL (subquery) — subquery references outer row.
• Similar to correlated but explicit.
• When useful. Per-outer top-N.

Slot 8 — 8-engine window support.

Engine	ROW_NUMBER	RANK	LAG/LEAD	RANGE INTERVAL
Postgres	✅	✅	✅	✅
MySQL 8+	✅	✅	✅	✅ (8+)
SQL Server	✅	✅	✅	✅ (from 2012)
Oracle	✅	✅	✅	✅
Snowflake	✅	✅	✅	✅
BigQuery	✅	✅	✅	✅
Databricks	✅	✅	✅	✅
DuckDB	✅	✅	✅	✅

Slot 9 — dialect matrix — the 5 categories.

Category	Portable	Non-portable notes
Correlated → JOIN	✅	universal
EXISTS/IN semantics	✅	NOT IN behaves same everywhere
DISTINCT alternatives	Mostly	DISTINCT ON = Postgres only
UNION vs UNION ALL	✅	universal
Window vs GROUP BY	✅	universal on modern engines

Slot 10 — combining rewrites.

• Layer techniques. JOIN + WINDOW + CTE for complex queries.
• Test incremental. Diff plans as you rewrite.

Worked example — running total via window

SELECT
  order_id,
  order_date,
  total,
  SUM(total) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS cumulative_total
FROM orders
ORDER BY order_date;

Rule of thumb. Cumulative sums = window with UNBOUNDED PRECEDING.

Worked example — rank per customer

SELECT
  customer_id,
  order_id,
  total,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rank_by_total
FROM orders;

Rule of thumb. Rank within group = window PARTITION BY.

Worked example — MATERIALIZED CTE forces a fence

WITH big_agg AS MATERIALIZED (
  SELECT customer_id, SUM(total) FROM orders GROUP BY customer_id
)
SELECT c.name, ba.total FROM customers c JOIN big_agg ba ON ba.customer_id = c.id;

Rule of thumb. Use MATERIALIZED when the CTE result is reused or large.

sql query rewriting patterns interview question — combining rewrites

A senior interviewer asks: "Refactor this query using CTE + window + JOIN rewrites."

Solution Using CTE + JOIN + window in composed rewrite

WITH customer_stats AS (
  SELECT
    customer_id,
    COUNT(*) AS order_count,
    SUM(total) AS total_revenue,
    MAX(created_at) AS last_order
  FROM orders
  GROUP BY customer_id
),
ranked AS (
  SELECT
    c.*,
    cs.*,
    ROW_NUMBER() OVER (PARTITION BY c.region ORDER BY cs.total_revenue DESC) AS revenue_rank
  FROM customers c
  LEFT JOIN customer_stats cs ON cs.customer_id = c.id
)
SELECT * FROM ranked WHERE revenue_rank <= 10;

Why this works — pre-aggregate in CTE, join in second CTE, window ranks. Top-10 per region.

SQL
Topic — optimization
SQL optimization drills

Practice →

SQL
Topic — window functions
Window function drills

Practice →

---

Cheat sheet — 20 rewrites recipe list

• Correlated → JOIN. LEFT JOIN + GROUP BY.
• Scalar subquery → LEFT JOIN + agg. Same idea.
• IN (subquery) → JOIN.
• NOT IN → NOT EXISTS. NULL-safe.
• NOT IN → LEFT JOIN + IS NULL. Anti-join.
• DISTINCT → GROUP BY. Same result.
• DISTINCT ON (Postgres). Top-1 per group.
• ROW_NUMBER() = 1. Portable top-N per group.
• UNION → UNION ALL. When dedup not needed.
• MAX(col) GROUP BY id → ROW_NUMBER OVER PARTITION.
• Self-JOIN for adjacent → LAG/LEAD window.
• col IN (a, b, c) → col = ANY(ARRAY[...]) on Postgres for large lists.
• WITH cte AS (...) → AS MATERIALIZED when needed.
• Multi-JOIN big intermediate → subquery per join.
• WHERE EXTRACT(...) → range predicate (sargable).
• ORDER BY RANDOM() → TABLESAMPLE (large samples).
• COUNT(*) estimate → pg_class.reltuples.
• SUM(x) on all-NULL → COALESCE(SUM(x), 0).
• Subquery in SELECT → LATERAL JOIN.
• Nested subqueries → CTE chain.

Frequently asked questions

When should I rewrite a correlated subquery to a JOIN?

Almost always. Correlated subqueries in the SELECT or WHERE clause can be evaluated per outer row — worst case O(N × M) — while a JOIN gives the optimiser choice among hash-join, merge-join, or nested-loop and typically produces an O(N + M) plan. Modern optimisers unnest simple correlated forms automatically, but manual rewrite gives you a predictable plan. Exception: subquery over a tiny dim table where the cost is negligible and correlated form is clearer.

Why does NOT IN return zero rows when the subquery has NULL?

Because x NOT IN (1, 2, NULL) expands to x != 1 AND x != 2 AND x != NULL. The last comparison is x != NULL which is UNKNOWN. AND with UNKNOWN never returns TRUE (it can be FALSE if another comparison is FALSE, or UNKNOWN otherwise). WHERE drops UNKNOWN. So any row that doesn't match a non-NULL value fails the check entirely. Fix: use NOT EXISTS, which handles NULL correctly, or filter NULL from the subquery.

When is UNION preferred over UNION ALL?

Only when the branches produce duplicate rows AND you must dedup them. Otherwise, use UNION ALL — it's always cheaper. UNION forces a sort+dedup step; UNION ALL just concatenates. On disjoint branches (e.g., active vs archive tables), UNION ALL is correct and fast. On overlapping branches where dedup is intentional, UNION is right but expensive — consider dedup at ingestion instead.

What's the difference between DISTINCT and GROUP BY?

Semantically equivalent when no aggregates are involved. SELECT DISTINCT a, b FROM t = SELECT a, b FROM t GROUP BY a, b. Cost is usually the same; some optimisers pick different plans, so profile if it matters. Preference: use GROUP BY when you also compute aggregates in the same query; use DISTINCT when the intent is just "unique rows." Neither is inherently faster.

When should I use WITH ... AS MATERIALIZED?

On Postgres 12+, CTEs inline by default (planner may push filters and merge with outer query). Use AS MATERIALIZED to force a fence when: (1) the CTE result is referenced multiple times and you don't want re-computation, (2) the CTE result is expensive and you want it computed once, or (3) you want to prevent the planner from pushing filters into the CTE (rare intent). Without MATERIALIZED, Postgres may inline your CTE — which is usually a win but occasionally not.

How do I decide between window function and GROUP BY for top-N per group?

Use window function (ROW_NUMBER() OVER (PARTITION BY key ORDER BY tiebreak)) for top-N per group when N > 1 or when you need to preserve non-key columns. Use MAX() GROUP BY key for aggregate-only per group. GROUP BY collapses rows; window preserves them. For N=1 on Postgres, DISTINCT ON is often the most elegant. For portable code across engines, use ROW_NUMBER — universally supported.

Practice on PipeCode

• Drill the SQL optimization practice library → — 450+ DE-focused questions covering query rewriting, plan diffing, and performance tuning.
• Sharpen SQL practice library → — general SQL surface.
• Layer SQL join drills → — most rewrites turn something else into a join.
• Warm up with SQL aggregation drills → — GROUP BY, DISTINCT, and UNION patterns.
• Layer window function drills → — window rewrites replace many subquery patterns.
• For the broader SQL interview surface, take the SQL for Data Engineering course →.

Pipecode.ai is Leetcode for Data Engineering — every `sql query rewriting patterns` recipe above ships with hands-on practice rooms where you take a correlated subquery running 40 seconds, rewrite it to a hash-joined 400 ms query, migrate a `NOT IN (subquery with NULL)` bug to `NOT EXISTS`, replace `DISTINCT` with `DISTINCT ON` or `ROW_NUMBER() = 1` for top-N per group, replace `UNION` with `UNION ALL` when dedup isn't needed, and finally force a CTE fence with `WITH ... AS MATERIALIZED` — the exact 20-pattern rewrite fluency that senior DE interviews probe. PipeCode pairs every query-rewriting concept with 450+ DE-focused problems and a real-time scoring engine, so you never have to wonder whether your `exists vs in` / `correlated subquery to join` / `distinct alternative` answer holds up under a senior interviewer's depth probes.

Practice optimization now →
SQL library →