ROW_NUMBER, PARTITION BY, recursive CTEs, EXISTS vs IN.

SQL Patterns — Window Functions, CTEs, and Lateral Joins

Advanced SQL patterns replace imperative loops with set-based operations. Window functions (ROW_NUMBER, RANK, LAG, LEAD) compute across rows without collapsing them like GROUP BY. CTEs (WITH clauses) decompose complex queries and enable recursive tree/hierarchy queries. Lateral joins let a subquery reference outer row columns. EXISTS is usually faster than IN for correlated subqueries because it short-circuits on first match.

Interactive Pipeline Flow

UnderstandImplementOptimise

📊 FROM — base tables / CTEs

CTEs (WITH clauses) act as named subqueries. In Postgres, CTEs are optimization fences by default — use MATERIALIZED/NOT MATERIALIZED to control.

Architecture Diagrams

Recursive CTE for hierarchical org chart traversal.

Loading diagram...

Key Design Decisions

🪟Window functions replace self-joins

LAG(salary) OVER (PARTITION BY dept ORDER BY hire_date) gets previous employee salary without a self-join. Same performance, far more readable.

🔎EXISTS beats IN for correlated subqueries

SELECT * FROM orders WHERE EXISTS (SELECT 1 FROM returns WHERE returns.order_id = orders.id) short-circuits. IN builds the full set first.

📄Keyset pagination over OFFSET

OFFSET 10000 scans and discards 10,000 rows. Keyset: WHERE id > 10000 ORDER BY id LIMIT 20 uses the index directly — O(log n) vs O(n).

Discussion