Advanced SQL: Window Functions, CTEs, and Views

The key distinction from GROUP BY

-- GROUP BY: collapses many rows into one row per department
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
-- Output: one row PER department -- individual employee rows are gone.

-- Window function: keeps every employee row, adds a computed column
SELECT name, department, salary,
       AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees;
-- Output: one row PER EMPLOYEE, each annotated with their department's average.

This is the core value proposition: you can compare an individual row to an aggregate of its group (salary vs. dept_avg_salary) in the same row, something plain GROUP BY can't do without a self-join or subquery.

Anatomy of the OVER clause

function_name(...) OVER (
    [PARTITION BY column1, column2, ...]   -- groups rows, like GROUP BY, but doesn't collapse them
    [ORDER BY column3, ...]                -- defines row order within each partition
    [ROWS/RANGE BETWEEN ... AND ...]       -- defines the "frame" -- which rows within the partition to include
)

• PARTITION BY — optional; without it, the whole result set is one partition. Divides rows into groups for the function to operate over, analogous to GROUP BY but without reducing row count.
• ORDER BY (inside OVER) — defines the order used for ranking functions (ROW_NUMBER, RANK) and for frame-relative functions (LAG, running totals). This is independent of the query's outer ORDER BY.
• Frame clause (ROWS BETWEEN ...) — defines exactly which rows within the partition the function sees relative to the current row (e.g., "from the start of the partition to the current row," for a running total).

Common window functions

ROW_NUMBER() OVER (...)         -- sequential number, no ties
RANK() OVER (...)               -- rank with gaps after ties
DENSE_RANK() OVER (...)         -- rank without gaps after ties
LAG(col, n) OVER (...)          -- value from n rows before the current row
LEAD(col, n) OVER (...)         -- value from n rows after the current row
SUM(col) OVER (...)             -- running/partitioned sum (not collapsed)
AVG(col) OVER (...)             -- running/partitioned average
FIRST_VALUE(col) OVER (...)     -- first value in the frame

Why this matters

Window functions are the standard, efficient way to express "rank within group," "running total," "percent of group total," or "compare to previous row" — all queries that, before window functions existed, required awkward self-joins or correlated subqueries (see the correlated subquery and self-join questions) that were both harder to read and often slower. They execute logically after WHERE/GROUP BY/HAVING but before ORDER BY/LIMIT in the query's execution order (see the execution order question), which is why you generally can't filter directly on a window function's result in the same query's WHERE clause without wrapping it in a subquery or CTE.

Side-by-side example

SELECT
    name, score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK()       OVER (ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM contestants;

• ROW_NUMBER(): Bob and Carol are tied at 90, but get distinct numbers (2, 3) anyway — the tie is broken arbitrarily (or deterministically, if you add more ORDER BY columns to fully disambiguate). No two rows ever share a ROW_NUMBER().
• RANK(): Bob and Carol both get rank 2 (tied), and Dave — the next distinct value — gets rank 4, skipping 3 entirely, because two rows already "used up" ranks 2 and 3.
• DENSE_RANK(): Bob and Carol both get rank 2, and Dave gets rank 3 — no gap, because dense rank only increments for each distinct value encountered, not for each row.

When to use which

• ROW_NUMBER(): when you need a strictly unique sequential identifier per row regardless of ties — e.g., picking exactly one "first" row per group (deduplication, pagination, "keep only the latest record per customer").
• RANK(): when ties should share a rank, and you want the rank to reflect "how many rows are strictly better," including the tie count — e.g., a leaderboard where two people tied for 2nd place means the next person really is in "4th place" by count.
• DENSE_RANK(): when ties should share a rank, but you want ranks to be a compact, gapless sequence — e.g., "top 3 distinct price tiers," where you care about distinct values, not row counts.

A common use: "top N per group"

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
) ranked
WHERE rn <= 3;   -- top 3 highest-paid employees PER department

This pattern (ROW_NUMBER() + PARTITION BY + filter in an outer query) is one of the most common real-world uses of window functions, and a frequent live-coding interview exercise.

The problem: unknown-depth hierarchies

-- employees: id, name, manager_id

Finding "all of Alice's direct reports" is a simple join. Finding "everyone in Alice's entire management chain below her, at any depth" can't be expressed with a fixed number of joins, because the org chart's depth varies and isn't known in advance.

Anatomy of a recursive CTE

WITH RECURSIVE subordinates AS (
    -- Base case (anchor member): the starting row(s)
    SELECT id, name, manager_id, 1 AS depth
    FROM employees
    WHERE id = 1   -- Alice's id

    UNION ALL

    -- Recursive member: references the CTE itself
    SELECT e.id, e.name, e.manager_id, s.depth + 1
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

How it executes conceptually:

1. Run the base case (anchor) once — this seeds the initial working set (Alice herself).
2. Run the recursive member using only the newest rows added in the previous iteration, producing a new batch of rows (Alice's direct reports).
3. Repeat step 2, each time joining against only the previous iteration's new rows, until an iteration produces zero new rows — then stop.
4. UNION ALL all iterations' results together as the final output.

Termination

The recursion must eventually stop producing new rows, or it runs forever (or until an engine-enforced recursion limit is hit — PostgreSQL doesn't have a hard limit by default and will happily loop indefinitely on a cyclic graph without one; SQL Server defaults to a 100-level MAXRECURSION limit specifically to guard against this). For genuinely cyclic data (e.g., a graph where cycles are possible, unlike a strict tree), you must explicitly track visited nodes and exclude them to avoid infinite recursion:

WITH RECURSIVE paths AS (
    SELECT start_node, end_node, ARRAY[start_node] AS visited
    FROM edges WHERE start_node = 'A'

    UNION ALL

    SELECT e.start_node, e.end_node, p.visited || e.end_node
    FROM edges e
    JOIN paths p ON e.start_node = p.end_node
    WHERE NOT e.end_node = ANY(p.visited)   -- prevents revisiting a node, avoiding infinite loops
)
SELECT * FROM paths;

Common use cases

• Org charts / management chains (as above).
• Category/product hierarchies (find all subcategories under "Electronics," arbitrarily nested).
• Bill-of-materials explosions (a product made of sub-assemblies, made of sub-sub-assemblies...).
• Graph traversal — shortest/all paths between two nodes, dependency resolution.

Performance note

Recursive CTEs can be slow on deep or wide hierarchies since each level is a fresh join pass; for read-heavy, rarely-changing hierarchies, a materialized path or closure table (precomputed ancestor/descendant pairs, maintained on write) is a common denormalization that trades write complexity for much faster read queries — worth mentioning as the production alternative when a recursive CTE becomes a bottleneck.

Regular view — a saved query, no storage

CREATE VIEW active_customer_totals AS
SELECT c.id, c.name, SUM(o.total) AS lifetime_total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.status = 'active'
GROUP BY c.id, c.name;

SELECT * FROM active_customer_totals WHERE lifetime_total > 1000;

Querying the view re-runs the underlying JOIN/GROUP BY every single time — it's purely a naming/abstraction convenience (hiding query complexity, centralizing a business definition, or restricting column access for security) with zero performance benefit over just writing the underlying query directly. It's always exactly as current as the base tables, because there's no cached/stored copy at all.

Materialized view — stored, physical results

CREATE MATERIALIZED VIEW active_customer_totals_mv AS
SELECT c.id, c.name, SUM(o.total) AS lifetime_total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.status = 'active'
GROUP BY c.id, c.name;

-- Reading this is now a simple table scan/index lookup -- no join/aggregation cost
SELECT * FROM active_customer_totals_mv WHERE lifetime_total > 1000;

-- But it's now a snapshot -- must be explicitly refreshed to reflect new data
REFRESH MATERIALIZED VIEW active_customer_totals_mv;

By default, REFRESH in PostgreSQL takes a lock that blocks concurrent reads during the refresh; REFRESH MATERIALIZED VIEW CONCURRENTLY (which requires a unique index on the view) avoids that at the cost of a slower refresh.

Key tradeoffs

When to use each

• Regular view: encapsulating a complex or security-sensitive query behind a simple name, with no performance goal — freshness matters more than read speed, or the underlying query is cheap enough that re-running it is a non-issue.
• Materialized view: an expensive aggregation/join that's read far more often than the underlying data changes — dashboards, reporting rollups, expensive analytics queries — where a controlled amount of staleness (refreshed hourly, nightly, or on a trigger) is an acceptable tradeoff for dramatically faster reads.

Refresh strategies

Scheduled (cron + REFRESH MATERIALIZED VIEW), triggered (refresh after a batch ETL job completes), or, in engines/extensions that support it, incremental refresh (only recomputing the delta rather than the whole view) — full refresh is the simplest but scales poorly if the underlying query is expensive and the view is large.

The starting shape: long/narrow data

-- sales: region, quarter, amount
-- ('East', 'Q1', 100), ('East', 'Q2', 150), ('West', 'Q1', 80), ('West', 'Q2', 120)

Goal: turn this into one row per region, with a column per quarter.

Approach: conditional aggregation (portable across all major engines)

SELECT
    region,
    SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS q1,
    SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS q2,
    SUM(CASE WHEN quarter = 'Q3' THEN amount ELSE 0 END) AS q3,
    SUM(CASE WHEN quarter = 'Q4' THEN amount ELSE 0 END) AS q4
FROM sales
GROUP BY region;

This works in every SQL engine, doesn't require any extension, and is the pattern most interviewers expect as the "default" answer, since it demonstrates understanding of conditional aggregation rather than reliance on engine-specific syntax.

Engine-specific dedicated syntax

SQL Server's PIVOT:

SELECT region, [Q1], [Q2], [Q3], [Q4]
FROM sales
PIVOT (SUM(amount) FOR quarter IN ([Q1], [Q2], [Q3], [Q4])) AS p;

PostgreSQL's crosstab() (requires the tablefunc extension):

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab(
    'SELECT region, quarter, amount FROM sales ORDER BY 1, 2'
) AS ct(region text, q1 numeric, q2 numeric, q3 numeric, q4 numeric);

The key limitation: pivoted columns must generally be known ahead of time

All three approaches require you to know the distinct values that will become columns (Q1..Q4) at query-authoring time — SQL is a fixed-schema language, so a query can't natively produce a variable number of output columns based on data it hasn't seen yet. If the pivoted values are truly dynamic (unknown until runtime), you need to generate the SQL dynamically in application code or via dynamic SQL (EXECUTE/sp_executesql) — a real limitation worth mentioning, since it's a common follow-up question ("what if you don't know the quarters in advance?").