A CTE (WITH name AS (...)) gives a query a named, temporary result set that later parts of the same statement can read, and a window function computes a value for each row using a set of related rows without collapsing them the way GROUP BY does. Interviewers reach for them constantly ("second-highest salary", "top 3 per group", "running total", "walk an org chart") because a candidate who only knows GROUP BY ends up with self-joins and correlated subqueries that are harder to read and often harder for the optimizer. For a Backend or AI engineer they are the everyday tools for analytics endpoints, leaderboards, deduplication jobs, de-duplicating ingested documents, and "latest row per entity" queries.
-- Test data (sqlite3 :memory:): employees(id, name, dept, salary)
-- Asha Eng 150 | Ben Eng 150 | Chen Eng 120 | Dara Eng 100
-- Eli Sales 90 | Fay Sales 80 | Gus Sales 80 | Hana Ops 70
SELECT dept, AVG(salary) AS avg_sal FROM employees GROUP BY dept;
-- dept avg_sal
-- Eng 130.0 <- 4 Eng rows became 1 row
-- Ops 70.0
-- Sales 83.3333333333333
SELECT name, dept, salary,
AVG(salary) OVER (PARTITION BY dept) AS dept_avg
FROM employees;
-- name dept salary dept_avg
-- Asha Eng 150 130.0 <- every input row survives
-- Ben Eng 150 130.0 and gets the group's average
-- Chen Eng 120 130.0 attached to it
-- Dara Eng 100 130.0
-- Hana Ops 70 70.0
-- Eli Sales 90 83.3333333333333
-- Fay Sales 80 83.3333333333333
-- Gus Sales 80 83.3333333333333Conceptually the database first produces the ordinary result rows (after FROM, WHERE, GROUP BY and HAVING), and only then evaluates window functions over that set. PARTITION BY dept says "treat each department as its own independent group", ORDER BY inside OVER says how rows are ordered within the group, and an optional frame says which neighbouring rows the function sees. The row count never changes, which is exactly why you can show "this employee's salary next to the department average" in one pass, something plain GROUP BY can only do by joining the aggregate back to the detail rows.
This evaluation order also explains the most common error: a window function cannot appear in WHERE, because WHERE runs before it exists. SQLite reports misuse of window function ROW_NUMBER(); PostgreSQL and SQL Server likewise refuse it. The fix is to compute the window value in a subquery or CTE and filter in the outer query, which is the reason CTEs and window functions appear together so often.
SELECT name, dept, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS drnk
FROM employees ORDER BY dept, salary DESC, name;
-- name dept salary rn rnk drnk
-- Asha Eng 150 1 1 1
-- Ben Eng 150 2 1 1 <- tie: same RANK, different ROW_NUMBER
-- Chen Eng 120 3 3 2 <- RANK skips to 3, DENSE_RANK does not
-- Dara Eng 100 4 4 3
-- Hana Ops 70 1 1 1
-- Eli Sales 90 1 1 1
-- Fay Sales 80 2 2 2
-- Gus Sales 80 3 2 2All three number rows within a partition according to the ORDER BY; they differ only in how they treat ties. ROW_NUMBER() always hands out 1, 2, 3, ... with no repeats, so when two rows tie, which one gets the lower number is not defined by the salary, and unless you add a tiebreaker column the choice can vary between runs or engines. RANK() gives tied rows the same number and then leaves a gap (1, 1, 3), the way sports rankings work. DENSE_RANK() gives tied rows the same number with no gap (1, 1, 2), so "rank 2" always means "the second-distinct value".
The choice is a semantic decision, not a style preference. "Second-highest salary" usually means the second distinct salary, so DENSE_RANK() = 2 is right; ROW_NUMBER() = 2 returns one of the two people tied for first when there is a tie. Being explicit about that ambiguity in an interview is worth more than reciting the syntax.
-- Top 3 salary levels per department (ties included) with DENSE_RANK
WITH ranked AS (
SELECT name, dept, salary,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS d
FROM employees)
SELECT name, dept, salary, d FROM ranked WHERE d <= 3 ORDER BY dept, d, name;
-- name dept salary d
-- Asha Eng 150 1
-- Ben Eng 150 1
-- Chen Eng 120 2
-- Dara Eng 100 3
-- Hana Ops 70 1
-- Eli Sales 90 1
-- Fay Sales 80 2
-- Gus Sales 80 2
-- Exactly 2 rows per department with ROW_NUMBER (deterministic via tiebreaker id)
WITH ranked AS (
SELECT name, dept, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC, id) AS rn
FROM employees)
SELECT name, dept, salary, rn FROM ranked WHERE rn <= 2 ORDER BY dept, rn;
-- Asha Eng 150 1 | Ben Eng 150 2 | Hana Ops 70 1 | Eli Sales 90 1 | Fay Sales 80 2
-- (Gus, tied with Fay at 80, is cut off - ROW_NUMBER forces a winner)
-- Second-highest salary, three ways (all return 120)
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
SELECT salary FROM (
SELECT DISTINCT salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS d FROM employees)
WHERE d = 2;
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;
-- The trap: ROW_NUMBER() = 2 with a tie for first
-- WITH r AS (SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) rn FROM employees)
-- SELECT name, salary FROM r WHERE rn = 2; -- returns Ben 150, which is the highest, not second-highestThe pattern for "top N per group" is always the same three moves: number rows inside each partition, put that in a CTE or subquery, then filter on the number in the outer query. Without window functions you would need a correlated subquery counting how many rows in the same group have a higher value, which reads worse and typically re-scans the group for every row. The pattern also shows why the answer to "top 3" depends on a product decision: "three rows" means ROW_NUMBER, "three distinct salary levels including ties" means DENSE_RANK, and "positions on a leaderboard with gaps" means RANK.
For the overall second-highest salary, the MAX-below-MAX version is portable and cheap but does not generalise to Nth; the DENSE_RANK version generalises to any N and any group; LIMIT ... OFFSET is short but returns no row (not NULL) if there is no second value and is engine-specific syntax (SQL Server uses OFFSET ... FETCH or TOP).
-- sales(day, amount): (1,100) (2,150) (3,90) (4,200) (5,120)
SELECT day, amount,
SUM(amount) OVER (ORDER BY day) AS running_total,
LAG(amount) OVER (ORDER BY day) AS prev_amount,
amount - LAG(amount) OVER (ORDER BY day) AS delta,
AVG(amount) OVER (ORDER BY day
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg3
FROM sales;
-- day amount running_total prev_amount delta moving_avg3
-- 1 100 100 NULL NULL 100.0
-- 2 150 250 100 50 125.0
-- 3 90 340 150 -60 113.333333333333
-- 4 200 540 90 110 146.666666666667
-- 5 120 660 200 -80 136.666666666667
-- Default-frame gotcha with ties (Eng dept, ordered by salary DESC)
SELECT name, salary,
SUM(salary) OVER (ORDER BY salary DESC) AS default_frame,
SUM(salary) OVER (ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame
FROM employees WHERE dept = 'Eng' ORDER BY salary DESC, name;
-- name salary default_frame rows_frame
-- Asha 150 300 150 <- default frame treats tied peers as one step
-- Ben 150 300 300
-- Chen 120 420 420
-- Dara 100 520 520When a window has an ORDER BY and no explicit frame, the SQL standard default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and "current row" in RANGE mode includes all rows that tie with it. That is why Asha and Ben both show 300 above: the database adds both 150s together at once. If you want a strict row-by-row running total, write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW or break ties with a unique column in the ORDER BY. Also note that LAG/LEAD return NULL at the edge, so delta is NULL on the first row; both accept an optional default argument.
-- Non-recursive: name a step so the query reads top-to-bottom
WITH dept_avg AS (
SELECT dept, AVG(salary) AS a FROM employees GROUP BY dept),
above AS (
SELECT e.name, e.dept, e.salary, d.a
FROM employees e JOIN dept_avg d USING (dept)
WHERE e.salary > d.a)
SELECT * FROM above ORDER BY dept, name;
-- name dept salary a
-- Asha Eng 150 130.0
-- Ben Eng 150 130.0
-- Eli Sales 90 83.3333333333333
-- Recursive: walk a hierarchy stored as (id, name, boss_id)
WITH RECURSIVE chain(id, name, depth, path) AS (
SELECT id, name, 0, name FROM org WHERE boss_id IS NULL -- anchor
UNION ALL
SELECT o.id, o.name, c.depth + 1, c.path || ' > ' || o.name -- recursive step
FROM org o JOIN chain c ON o.boss_id = c.id)
SELECT depth, path FROM chain ORDER BY path;
-- depth path
-- 0 CEO
-- 1 CEO > VP Eng
-- 2 CEO > VP Eng > Lead
-- 3 CEO > VP Eng > Lead > Dev
-- 1 CEO > VP Sales
-- 2 CEO > VP Sales > RepA plain CTE is about readability and reuse within one statement: it lets you name an intermediate result instead of nesting subqueries, and you can chain several separated by commas. It is not a stored table, it does not persist past the statement, and it is not automatically an optimisation barrier or a cache. In PostgreSQL 12 and later, a non-recursive CTE that is referenced once is generally inlined into the outer query (you can force old behaviour with MATERIALIZED); before 12 it was always materialised, which surprised people who assumed the planner would push filters into it. Other engines vary, so do not claim "a CTE is faster" or "a CTE is slower" without naming the engine and checking the plan.
A recursive CTE has an anchor query (the starting rows), then a recursive step joined to the CTE itself, repeated until it produces no new rows. That is how you walk org charts, category trees, bill-of-materials, or a graph of dependencies. If the data has a cycle, the recursion never terminates unless you guard it (a depth limit, a UNION instead of UNION ALL where supported, or a path check); many engines also have a recursion limit setting.
GROUP BY collapses rows. Windows are evaluated after WHERE/GROUP BY/HAVING, so you cannot filter on them in WHERE; wrap them in a CTE or subquery.PARTITION BY splits rows into independent groups, ORDER BY inside OVER orders rows within each group, and an optional frame (ROWS/RANGE) limits which rows the function sees.ROW_NUMBER never ties (add a tiebreaker for deterministic output), RANK ties then leaves gaps, DENSE_RANK ties without gaps. Pick based on what "second" or "top 3" should mean when values tie.DENSE_RANK() = 2 (or MAX below MAX, or DISTINCT ... LIMIT 1 OFFSET 1).ORDER BY and no explicit frame, the default frame is RANGE ... CURRENT ROW, which lumps ties together; use ROWS for a strict row-by-row running total.LAG/LEAD read the previous/next row in the window and return NULL (or your default) at the edges: the standard tool for deltas and gap detection.FROM and JOIN, then WHERE, then GROUP BY and HAVING to produce the base rows.PARTITION BY expressions, typically by sorting or hashing on them.ORDER BY (this sort is usually the main cost, and an index matching partition + order columns can let some engines skip it).SELECT list, DISTINCT, ORDER BY and LIMIT run, which is why filters on window results need a wrapping CTE or subquery.-- Run with: sqlite3 :memory: (window functions need SQLite 3.25+, and this was verified on 3.51)
CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER);
INSERT INTO employees VALUES
(1,'Asha','Eng',150),(2,'Ben','Eng',150),(3,'Chen','Eng',120),(4,'Dara','Eng',100),
(5,'Eli','Sales',90),(6,'Fay','Sales',80),(7,'Gus','Sales',80),(8,'Hana','Ops',70);
-- 1) Compare the three ranking functions on the Eng tie
SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC, id) AS rn,
RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employees WHERE dept = 'Eng';
-- Expected: Asha rn=1 rnk=1 drnk=1 | Ben rn=2 rnk=1 drnk=1
-- Chen rn=3 rnk=3 drnk=2 | Dara rn=4 rnk=4 drnk=3
-- 2) Second-highest salary company-wide
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;
-- Expected: 120
-- 3) Break it: use a window function in WHERE
SELECT name FROM employees WHERE ROW_NUMBER() OVER (ORDER BY salary) = 1;
-- Expected error in SQLite: misuse of window function ROW_NUMBER()
-- Now fix it by wrapping in a CTE:
WITH r AS (SELECT name, ROW_NUMBER() OVER (ORDER BY salary, id) AS rn FROM employees)
SELECT name FROM r WHERE rn = 1;
-- Expected: Hana
-- 4) Recursive CTE: numbers 1..5
WITH RECURSIVE n(x) AS (SELECT 1 UNION ALL SELECT x + 1 FROM n WHERE x < 5)
SELECT x FROM n;
-- Expected: 1 2 3 4 5 (one per row)
Q: What is a CTE?
A: A Common Table Expression is a named result set defined with WITH name AS (...) that exists only for the duration of one statement and can be referenced by the main query or by later CTEs. It mainly buys readability, reuse of an intermediate step, and (with RECURSIVE) hierarchy traversal. The trade-off is that it is not a cache or an index: in PostgreSQL 12+ single-use CTEs are usually inlined, in older versions they were materialised, so it can help or hurt performance depending on the engine, and you should check the plan.
Q: What are window functions?
A: They compute a value for each row over a set of related rows defined by OVER (PARTITION BY ... ORDER BY ... frame), without collapsing those rows like GROUP BY does. Examples are ranking (ROW_NUMBER, RANK, DENSE_RANK), aggregates over a window (SUM() OVER), and row access (LAG, LEAD). The cost is that the engine must partition and usually sort the data, which on large inputs can be expensive or spill to disk, so an index that matches the partition and order columns can matter.
Q: How would you find the second-highest salary?
A: Clarify ties first: if "second-highest" means the second distinct value, use DENSE_RANK() OVER (ORDER BY salary DESC) in a subquery and pick rank 2, or MAX(salary) WHERE salary < (SELECT MAX(salary)), or SELECT DISTINCT ... ORDER BY salary DESC LIMIT 1 OFFSET 1. Avoid ROW_NUMBER() = 2 unless you really want the second row, because with two people tied at the top it returns someone at the highest salary. The DENSE_RANK form generalises to Nth and to "per department", while the MAX form is the cheapest for exactly second.
Q: How would you find the top 3 records per group?
A: Rank inside each group in a CTE (ROW_NUMBER() OVER (PARTITION BY group ORDER BY metric DESC)) and filter rn <= 3 in the outer query, since you cannot filter on a window function in WHERE. Use ROW_NUMBER for exactly 3 rows with a deterministic tiebreaker, or DENSE_RANK if ties should all be kept. On big tables an index on (group, metric DESC) helps, and some engines (PostgreSQL) can also use a LATERAL join to fetch top-N per group when there are few groups and a good index.
Follow-up: Your top-3-per-group query is slow on 200 million rows. What do you look at?
A: Read the plan first: the usual cost is a sort of the whole table by (partition, order) columns, possibly spilling to disk. An index on (group_col, metric DESC) can let the engine read rows already in order and stop early in some plans; otherwise reduce the input before ranking (filter by date range, pre-aggregate) or, when the group list is small, use a LATERAL/CROSS APPLY subquery with ORDER BY ... LIMIT 3 per group. The trade-off is extra index storage and write cost against read speed.
Follow-up: Is a CTE the same as a temp table or a view?
A: No. A view is a stored query definition reusable across statements, a temp table physically holds data for a session and can be indexed and analysed, and a CTE lives only inside one statement. A temp table can be the better choice when an intermediate result is expensive and reused many times, because the planner gets real statistics for it; the cost is the write and a two-step workflow.
Follow-up: What is the difference between ROWS and RANGE in a window frame?
A: ROWS counts physical rows relative to the current row, while RANGE is based on the value of the ORDER BY column, so all peers with equal values are treated as the current row together. With ties, a default-frame running total jumps in steps (both 150s show 300 in the example above) whereas a ROWS frame advances one row at a time. Not every engine supports every RANGE offset form, so check the docs for the engine you use.
Window functions and CTEs are how you keep logic in the database where the data is, and that decision has system-design consequences. A leaderboard, "latest event per device", "top products per category" or "deduplicate ingested rows keeping the newest" can each be one SQL statement using ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC) instead of pulling millions of rows into the application to sort them, which saves network transfer and memory. In an AI or RAG pipeline the same pattern removes duplicate document chunks by content hash or returns the best-scoring chunk per document before assembling context. The flip side is that these queries sort and partition large sets, so on a hot OLTP primary they should run against a replica, a materialised summary, or a precomputed table (in the spirit of CQRS read models) rather than being executed live for every page view. Recursive CTEs are a pragmatic answer for tree data (comments, categories, org charts) up to moderate depth, while very deep or heavily-connected graphs may justify a graph store or a closure/path column.
Window functions and CTEs trade raw simplicity for expressive power: they make ranking, running totals and hierarchical queries short and readable, but they hide real work (sorting, partitioning, possibly materialising) behind tidy syntax. A window over a big table without a supporting index is a full sort, and stacking several windows with different PARTITION BY/ORDER BY clauses can mean several sorts. A CTE is not a free performance boost and, depending on engine and version, may block or permit predicate pushdown, so a long chain of CTEs can be harder to tune than the equivalent joins. They are the wrong tool when a simple GROUP BY or an indexed ORDER BY ... LIMIT is enough, when the same heavy result is recomputed per request (precompute it instead), or when portability matters across older engines (window functions arrived in MySQL only in 8.0 and SQLite in 3.25). Always confirm behaviour with EXPLAIN on the actual engine rather than assuming.