SQL Window Functions Interview Questions

ROW_NUMBER() always produces distinct integers — no two rows ever share a number. When the ORDER BY is not deterministic (ties exist), the standard does not specify which tied row gets the lower number; the engine may assign them in any order, and that order can vary between executions. To get a stable, reproducible result, add a tiebreaker column (e.g. ORDER BY score DESC, id ASC) that makes the ordering fully deterministic.

Window functions are evaluated after WHERE and GROUP BY but before ORDER BY, so they cannot appear in a WHERE clause — the engine hasn't computed them yet at that stage. To filter on a window function result, wrap the query in a subquery or CTE and apply WHERE in the outer query.

ROW_NUMBER() always assigns a unique, consecutive integer — it never produces ties regardless of the data. The ORDER BY val DESC, name ASC inside the window sorts Ann and Bo (both 90) by name, so Ann gets 1, Bo gets 2, and Cy (80) gets 3. Compare this to RANK(), which would give 1, 1, 3, or DENSE_RANK(), which would give 1, 1, 2.

ROW_NUMBER() OVER (ORDER BY salary DESC) defines a single window over every row in the result, so numbering is 1, 2, 3 … across all departments. To restart numbering per department, add PARTITION BY department: ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC). Without PARTITION BY, the window function treats the entire result set as one partition.

Both functions assign rank 1 to the two employees tied at 100. RANK() then skips rank 2 (because two rows consumed positions 1 and 2) and assigns rank 3 to the 80 employee. DENSE_RANK() never skips — it increments by one for each distinct value — so the 80 employee gets rank 2. Use DENSE_RANK() when you want a contiguous sequence with no gaps; use RANK() when the number assigned should reflect how many rows scored higher.

PARTITION BY divides the rows into independent groups (like GROUP BY for window functions) — the function resets for each partition. Without it, the entire result set is treated as a single partition. So SUM(revenue) OVER (ORDER BY sale_date) computes a running total across all rows in sale_date order regardless of department, while the original version computes a per-department running total that resets each time the department_id changes.

RANK() assigns the same rank to tied rows and then skips ahead, producing gaps. For example, two rows tied at rank 1 are both given rank 1, and the next distinct value receives rank 3. DENSE_RANK() is the function that avoids gaps: it also ties equal rows but increments by one for the next distinct value.

PARTITION BY divides the result set into independent windows — ranking, aggregation, and frame boundaries all restart at 1 (or the aggregate's identity) for each partition. Without PARTITION BY, the entire result set is treated as one window and numbers run continuously from first to last row.

DENSE_RANK() assigns the same rank to ties but never skips rank values. Alice is uniquely first at 95, so she gets 1. Bob and Carol tie at 80 and both receive 2. Dave, with 70, is the next distinct score and gets 3 — not 4. This is the key difference from RANK(), which would have given Dave rank 4 because two rows occupied positions 2 and 3.

LAG(revenue) with no offset defaults to offset 1, returning the value from the immediately preceding row in window order. For Mar (the third row), the preceding row is Feb with revenue 120, so prev is 120. The Jan row has no predecessor and receives NULL. LEAD() would look in the opposite direction — returning the next row's value instead.

Window functions are evaluated after WHERE, so they cannot appear in a WHERE clause — the database raises an error (e.g. "window functions are not allowed in WHERE"). The fix is to wrap the window function in a CTE or subquery and filter in the outer query:

WITH ranked AS (
  SELECT department, employee_name, salary,
         RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT department, employee_name, salary
FROM ranked
WHERE rnk = 1;

GROUP BY reduces N rows in a group to one summary row, discarding the detail. A window function (OVER (...)) evaluates the same aggregate across a partition but does not collapse rows — every original row survives and gets the computed value appended. That row-preserving behaviour is exactly why window functions are reached for when you need both the detail and a group-level metric (average, total, rank) in the same result set.

PARTITION BY is the windowing analogue of GROUP BY: it divides rows into partitions and the function restarts for each one, so an aggregate or rank is scoped to its partition. ORDER BY within the OVER clause sequences rows inside a partition — it controls the order a running total accumulates in or the order a RANK assigns ranks in, but it does not itself create groups. With no PARTITION BY, the entire input is a single partition.

All three are ranking window functions over an ordered partition but differ on ties. ROW_NUMBER() always produces a unique sequential number, breaking ties arbitrarily. RANK() assigns equal rank to ties and then leaves a gap (1,1,1,4). DENSE_RANK() assigns equal rank to ties with no gap (1,1,1,2). Choosing wrongly is a classic top-N-per-group bug: ROW_NUMBER() returns exactly one row per group, while RANK()/DENSE_RANK() <= N returns all rows tied at the cutoff.

A window function computes an aggregate over a partition but attaches the result to each input row rather than collapsing them — so you get every order row plus its region's total in one pass, without a self-join. GROUP BY reduces each region to a single summary row, losing the individual rows. That row-preserving behaviour is the whole point of OVER (...).

ROWS mode defines the frame in terms of physical row offsets — CURRENT ROW means exactly this row, so each tied row has a different frame boundary. RANGE mode treats all rows with the same ORDER BY value as peers; CURRENT ROW in RANGE mode means all rows in the same peer group. As a result, both rows sharing order_date include each other in their frames when using RANGE, which can produce different aggregated values than ROWS. This distinction is especially important for running totals: ROWS gives a strict cumulative sum; RANGE can double-count the same-date rows.

When ORDER BY is specified without an explicit frame clause, SQL defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — a cumulative frame up to and including all rows that tie with the current row. The whole-partition frame (UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) is only used when there is no ORDER BY at all. This distinction matters for running totals: a cumulative SUM with ORDER BY behaves very differently from a partition-wide SUM.

RANK() gives tied rows the same rank and then skips the next ranks. Ann and Bo both score 90, so both rank 1; Cy scores 80 and gets rank 3 (not 2), because two rows already occupied positions 1 and 2. DENSE_RANK() would give Cy 2 (no gap), and ROW_NUMBER() would give 1, 2, 3 (no ties). The gap after a tie is exactly what distinguishes RANK from DENSE_RANK.

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING extends the frame to every row in the partition, so SUM always returns the partition total — not a running total. A running total requires the frame to end at the current row: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That is also the default frame when ORDER BY is present, so removing the explicit ROWS clause entirely fixes the query.

GROUP BY region reduces the result to one row per region before window functions run. At that point sale_id and amount are no longer meaningful individual values, so the database rejects them in the SELECT list (they are neither in the GROUP BY nor wrapped in an aggregate). Remove the GROUP BY entirely — window functions compute aggregates across rows without collapsing them, so SUM(amount) OVER (PARTITION BY region) already gives each row the region total while keeping every sale row intact.

A frame clause bounds which rows in the partition feed the aggregate. ROWS counts physical rows offset from the current row; RANGE works on the ORDER BY value and treats peers (rows with equal ordering values) as a single unit, including them all or none. They behave identically when the ORDER BY key is unique, so the distinction only surfaces with ties — where RANGE gives the same frame to every tied row and ROWS does not. The implicit default frame when you specify ORDER BY but no frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, a common source of surprise.