All topics
5 / 66
queries & joins

Subqueries, CTEs & Window Functions

Express multi-step queries cleanly with CTEs (WITH), per-row computations with window functions, and lookups with subqueries.

Three tools handle queries that need more than a filter and a join. They overlap, and picking the right one is mostly about readability and one real capability difference.

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 inside a query. It can sit in WHERE, in FROM as a derived table, or in the SELECT list.
WHEREFROMSELECT
2
The kind to watch is the correlated subquery — one that references the outer row, so it runs once per row:
3
    SELECT c.name,
           (SELECT COUNT(*) FROM orders o
             WHERE o.customer_id = c.id)   -- runs per customer
    FROM customers c
4
A million customers means a million executions. Often rewritable as a single join or window function.
5
A CTE names a step. WITH name AS (...) puts the subquery up front so the main query reads it like a table:
WITH name AS (...)
6
    WITH recent AS (
      SELECT * FROM orders WHERE created_at > '2026-01-01'
    ), totals AS (
      SELECT customer_id, SUM(total) AS spend FROM recent GROUP BY customer_id
    )
    SELECT * FROM totals WHERE spend > 10000
7
Same result as nesting three levels deep, read top to bottom instead of inside out.
8
CTEs also do something nothing else can: recursion, for hierarchies like an org chart or a category tree, where you cannot know in advance how many levels there are.
9
Window functions keep every row. This is the difference that matters. GROUP BY collapses a group into one row. A window function computes across the group and leaves the rows alone:
GROUP BY
10
    name    dept   salary   AVG(salary) OVER (PARTITION BY dept)
    Asha    eng     120        110
    Ravi    eng     100        110
    Meera   sales    90         90
11
Six values in, six rows out, each carrying its department average. With GROUP BY dept you would get two rows and lose every name.
GROUP BY dept
12
That is why window functions solve, in one line, problems that otherwise need a self-join:
13
  - `ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)` — rank within a group
  - `SUM(x) OVER (ORDER BY date)` — running total
  - `LAG(price) OVER (ORDER BY date)` — compare a row to the previous one
SUM(x) OVER (ORDER BY date)LAG(price) OVER (ORDER BY date)
14
Two practical cautions. A CTE is not always free — in some databases it is an optimisation fence, materialised rather than folded into the main query, so on a hot path check the plan rather than assuming. And filtering on a window function needs a wrapper, because WHERE runs before the window is computed:
WHERE
15
    SELECT * FROM (
      SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
      FROM employees
    ) t WHERE rn <= 3
16
What the interviewer is probing.1. "When is a window function better than a self-join?" Probing: the fit. Stalls: "Never." Moves up: for ranking, running totals and comparisons to neighbouring rows — it computes per row without collapsing or re-reading the table.
17
2. "What is the difference between a CTE and a subquery?" Probing: the practical answer. Stalls: "CTEs are faster." Moves up: mainly readability and reuse — though some engines materialise a CTE, which can help or hurt, and recursion requires one.
18
3. "What does a correlated subquery cost?" Probing: the hazard. Stalls: "Nothing." *Moves up:* it may execute per outer row, so it behaves like a loop — a join or window function is usually far cheaper.
19
4. "What is the difference between ROW_NUMBER, RANK and DENSE_RANK?" Probing: the detail. Stalls: "The same." Moves up: ROW_NUMBER is always unique; RANK leaves gaps after ties; DENSE_RANK does not.
Use cases:CTEWindowSubquery