queries & joins
Subqueries, CTEs & Window Functions
Express multi-step queries cleanly with CTEs (WITH), per-row computations with window functions, and lookups with subqueries.
As queries grow beyond simple filters and joins, three tools keep them readable and powerful: subqueries, common table expressions, and window functions. They overlap in what they can express but differ in clarity and capability, and knowing which to reach for is a mark of SQL fluency.
CTE = "let me define X first, then build with it." Window function = "for each row, look at the surrounding crowd and compute something."
Key Concepts
1
A subquery is a query nested inside another — in the WHERE clause (WHERE id IN (SELECT ...)), the FROM clause (a derived table), or the SELECT list. A correlated subquery references the outer row and runs once per row, which is expressive but can be slow. A common table expression, written with WITH name AS (...), names a subquery up front so the main query can reference it like a table; this flattens deeply nested logic into readable, sequential steps, lets you reuse the same intermediate result, and — uniquely — enables recursion for hierarchies like org charts or category trees. Window functions are the most distinctive: ROW_NUMBER(), RANK(), LAG()/LEAD(), and running SUM() OVER (PARTITION BY ... ORDER BY ...) compute a value across a set of rows related to the current row without collapsing them the way GROUP BY does. That is the key difference — an aggregate reduces a group to one row, while a window function keeps every row and adds the computed column alongside.
WHEREWHERE id IN (SELECT ...)FROMSELECTWITH name AS (...)
2
The interview-relevant points: window functions elegantly solve problems that otherwise need self-joins or correlated subqueries, such as "rank within each group," "running total," or "compare each row to the previous." CTEs improve readability and enable recursion but are sometimes an optimisation fence depending on the database, so for hot paths it is worth checking the plan. And a correlated subquery can frequently be rewritten as a join or a window function that the optimiser handles far more efficiently.