Window functions and the frame nobody specifies

A window function computes a value for each row from a set of related rows — its window — without collapsing them the way GROUP BY does.

SELECT id, dept, salary,
       AVG(salary) OVER (PARTITION BY dept) AS dept_avg
FROM employees;

PARTITION BY splits the rows into independent groups; ORDER BY inside OVER orders the rows within each partition. Window functions are evaluated after WHERE, GROUP BY and HAVING, so you cannot filter on one in WHERE. Compute it in a CTE or derived table and filter outside:

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC, id) AS rn
  FROM employees
)
SELECT * FROM ranked WHERE rn = 1;

Three ways to number rows

• ROW_NUMBER() gives 1, 2, 3, ... with no repeats. Tied rows get different numbers in an undefined order unless the ORDER BY is unique.
• RANK() gives tied rows the same number and then skips: 1, 2, 2, 4.
• DENSE_RANK() gives tied rows the same number without gaps: 1, 2, 2, 3.

1CREATE TABLE results (id INT PRIMARY KEY, player NVARCHAR(10) NOT NULL, points INT NOT NULL);2INSERT INTO results VALUES (1, N'Ana', 90), (2, N'Luka', 85), (3, N'Nino', 85), (4, N'Mari', 70);

1SELECT player, points,2       ROW_NUMBER() OVER (ORDER BY points DESC, id) AS row_number,3       RANK()       OVER (ORDER BY points DESC)     AS rank,4       DENSE_RANK() OVER (ORDER BY points DESC)     AS dense_rank,5       LAG(points)  OVER (ORDER BY points DESC, id) AS previous_points6FROM results7ORDER BY points DESC, id;

LAG(col, 1) OVER (ORDER BY ...) reads the previous row's value and LEAD the next one; both return NULL (or a default you pass as the third argument) at the edge of the partition. They replace the old self-join on id - 1, which breaks as soon as ids have gaps.

The frame, and the default that bites

Aggregates such as SUM over an ordered window also have a frame: which rows around the current one take part. If you write ORDER BY and no frame, SQL Server uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE works on values, not positions: every row that ties with the current row on the ORDER BY value is inside the frame. Two ledger entries on the same day therefore both show the total after both.

Write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and order by something unique. It counts physical rows and gives a true running total.

A ROWS frame can also have a fixed size: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW covers the current row and at most two before it in the partition, which is how a moving average is written. Near the start of a partition the frame simply holds fewer rows.

1CREATE TABLE ledger (id INT PRIMARY KEY, posted_on DATE NOT NULL, amount INT NOT NULL);2INSERT INTO ledger VALUES (1, '2024-03-01', 100), (2, '2024-03-02', 20), (3, '2024-03-02', 10), (4, '2024-03-03', -50);

1SELECT id, posted_on, amount,2       SUM(amount) OVER (ORDER BY posted_on) AS default_frame,3       SUM(amount) OVER (ORDER BY posted_on, id4                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame5FROM ledger6ORDER BY posted_on, id;

Common mistakes

• Running totals with the default frame. The second example shows two rows on 2 March both reading 130.
• ROW_NUMBER() over a non-unique order and then relying on which tied row got 1.
• Forgetting PARTITION BY. The total runs across all accounts instead of restarting for each one.
• Filtering a window result in WHERE. SQL Server rejects it; compute the value in a CTE and filter outside.

What interviewers ask

• Difference between RANK and DENSE_RANK? Both share numbers on ties; RANK leaves a gap afterwards, DENSE_RANK does not.
• What is the default window frame and why does it matter? With ORDER BY and no frame it is RANGE ... CURRENT ROW, which includes all peers of the current row; running totals need ROWS.
• How do you get the second-highest salary per department? DENSE_RANK() partitioned by department, then filter on 2 in an outer query.