Advanced SQL: Window Functions, CTEs, Recursive...

Window functions compute values across a set of rows related to the current row without collapsing the result set. Unlike GROUP BY, which produces one row per group, window functions keep every row and add a computed column. ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER, and NTILE are the most tested functions. The PARTITION BY clause defines the window groups. ORDER BY within the OVER clause defines the row ordering for ranking and running calculations.

CTEs define temporary named result sets that exist only for the duration of a single query. They replace subqueries and make complex SQL readable by breaking it into named steps. Each CTE is defined in the WITH clause and can reference CTEs defined before it. CTEs do not imply materialization: the optimizer may inline them, merge them, or execute them separately depending on the query plan.

Recursive CTEs query hierarchical or graph-structured data. The recursive CTE has two parts: the anchor member (base case) and the recursive member (the step that references the CTE itself). The database executes the anchor first, then repeatedly executes the recursive member until it produces no new rows. Common use cases: org charts, bill of materials, category trees, and shortest path calculations.

LATERAL allows a subquery in the FROM clause to reference columns from preceding tables. Without LATERAL, each subquery in FROM is independent. With LATERAL, the subquery runs once per row of the preceding table, similar to a correlated subquery but in the FROM clause. This is the most efficient way to get the top N per group pattern without window functions.

Writing correct SQL is step one. Writing fast SQL is step two. Query optimization requires understanding indexes (B-tree, hash, GIN, GiST), execution plans (EXPLAIN ANALYZE), join algorithms (nested loop, hash join, merge join), and common anti-patterns (SELECT *, functions on indexed columns, implicit type casts, correlated subqueries). Data engineers must optimize queries that run against tables with billions of rows.

UNION, INTERSECT, and EXCEPT combine or compare result sets. UNION stacks rows vertically. INTERSECT returns rows that appear in both result sets. EXCEPT returns rows from the first set that do not appear in the second. Each has an ALL variant that skips deduplication. These operations follow set theory and are tested in interviews as an alternative to JOIN-based solutions.

Dynamic SQL constructs and executes SQL strings at runtime. It is used for queries where the table name, column list, or filter conditions are not known until execution time. In PostgreSQL, EXECUTE with format() builds safe dynamic queries. In application code, parameterized queries (prepared statements) prevent SQL injection while allowing dynamic values. Data engineers use dynamic SQL for metadata-driven pipelines, automated schema migrations, and generic data quality checks.