Window Functions for Interviews: ROW_NUMBER, RANK and LAG

A window function calculates a value for each row using a set of related rows (its "window") without collapsing the rows the way GROUP BY does. Window functions power the classic interview problems: ranking, "Nth highest salary", top N per group, removing duplicates, running totals, and comparing a row with the previous one.

The OVER clause

function_name(...) OVER (
    PARTITION BY column     -- optional: restart the calculation for each group
    ORDER BY column         -- optional or required, depending on the function
    frame clause            -- optional: which rows around the current one to use
)

GROUP BY returns one row per group. A window function keeps every row and adds a column.

ROW_NUMBER, RANK and DENSE_RANK

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC, id) AS row_num,
       RANK()       OVER (ORDER BY salary DESC)     AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC)     AS dense_rnk
FROM employees
WHERE salary IS NOT NULL;

Ben and Carla tie on 75000, and that is where the three functions differ:

• ROW_NUMBER never repeats. Tied rows still get different numbers, in an arbitrary order unless you add a tie-breaker such as id.
• RANK gives tied rows the same rank and then skips numbers. After two rows at rank 2, the next rank is 4.
• DENSE_RANK gives tied rows the same rank and does not skip. The next rank is 3.

Nth highest salary

You cannot filter on a window function in WHERE, because windows are calculated after WHERE. Calculate the rank in a CTE (or subquery) and filter in the outer query:

WITH ranked AS (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
    FROM employees
    WHERE salary IS NOT NULL
)
SELECT DISTINCT salary
FROM ranked
WHERE salary_rank = 2;      -- change 2 to N

This returns 75000. DENSE_RANK is the right choice because "second highest salary" means the second highest distinct value, even when two people share the top salary. See the Nth highest salary snippet, which also shows an version.

For the specific case of the second highest, a classic alternative without window functions is .

Top N per group

"Highest-paid employee in each department" is the same trick with :

The numbering restarts for each department, so is the top earner of each. If tied employees should all be returned, use instead of .

Removing duplicates

Number the rows inside each group of duplicates and delete every row except the first. The full pattern is in delete duplicate rows and keep one. To only find them, see find duplicate rows.

LAG and LEAD: look at neighbouring rows

reads a value from a previous row, and from a following row. With a table:

The first row has no previous row, so returns . The full form is , for example to use 0 instead of .

Running totals

The frame () says which rows to add up: everything from the first row up to the current one. Always write it. If you leave it out, the default frame is , which treats rows with equal values as one group (so tied dates show the same total) and is slower in SQL Server. See the running total snippet.

Rules to remember

• Window functions are allowed only in and , not in , or .
• Ordinary aggregates (, , , , ) become window functions when you add .
• is optional. Without it, the whole result is one window.
• When ties matter, say how you handle them. Interviewers ask this on purpose.

Practise

1. Run the rank comparison on the practice data, then change to and predict the new ranks.
2. Find the second highest salary in each department.
3. Take the Logic, Subqueries and Windows quiz.