SQL Window Functions: ROW_NUMBER, RANK & PARTITION BY

A window function looks at other rows without collapsing them. GROUP BY answers "what is the average salary per department" by folding each department into one row. A window function answers "show me every employee, each with their department's average alongside" — all the rows survive, and each gets a value computed over its "window" of related rows. That one idea powers rankings, running totals, and the top-N-per-group questions that appear in almost every serious SQL interview.

Numbering rows: ROW_NUMBER ▶ runs here

SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
       first_name, salary
FROM Employees;

OVER is what makes a function a window function. Here the window is the whole table ordered by salary, and ROW_NUMBER hands out 1, 2, 3… down that order. Unlike ORDER BY alone, the position becomes a real value you can filter on — which is how "give me rows 11 to 20" pagination is built.

A group value on every row: AVG OVER PARTITION BY ▶ runs here

SELECT first_name, dept_id, salary,
       AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM Employees;

PARTITION BY is GROUP BY's non-destructive cousin: it splits the rows into per-department windows, computes the average within each, and stamps it on every row of that department. Twelve employees in, twelve rows out — each now carrying its own comparison point. Computing "who earns above their department's average" becomes a simple wrap of this query, no correlated subquery required.

Ranking with ties: RANK and DENSE_RANK

-- PostgreSQL / MySQL 8+ / SQL Server / SQLite 3.25+
SELECT first_name, salary,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM Employees;

All three numbering functions differ only in how they treat ties. ROW_NUMBER ignores them — equal rows still get different numbers, arbitrarily. RANK gives equal rows the same number and then skips (1, 2, 2, 4). DENSE_RANK gives equal rows the same number and does not skip (1, 2, 2, 3). Interviewers love asking for this difference; now you have it.

The interview classic: top earner per department

-- PostgreSQL / MySQL 8+ / SQL Server / SQLite 3.25+
WITH ranked AS (
  SELECT first_name, dept_id, salary,
         ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
  FROM Employees
)
SELECT first_name, dept_id, salary
FROM ranked
WHERE rn = 1;

"Highest paid employee in each department" — the question that separates people who know window functions from people who fight it with correlated subqueries. Rank within each partition, keep rank 1. Change rn = 1 to rn <= 3 and it is top three per department; that is the whole trick.

Running totals

-- PostgreSQL / MySQL 8+ / SQL Server / SQLite 3.25+
SELECT order_date, order_total,
       SUM(order_total) OVER (ORDER BY order_date) AS running_total
FROM Orders;

An aggregate with an ORDER BY inside OVER accumulates: each row's value is the sum of everything up to and including it. This is the cumulative-revenue chart every dashboard has, expressed in one line — and the same shape gives moving averages once you add a frame clause like ROWS BETWEEN 6 PRECEDING AND CURRENT ROW.

Window functions vs GROUP BY, in one sentence each

GROUP BY collapses rows to one per group and can only return group-level columns. Window functions keep every row and attach group-level (or position-level) values to each. When the question starts with "for each row…", it is a window function; when it starts with "how many / what total per group…", it is GROUP BY.

Frequently asked questions

Related reading

• SQL CTEs Explained: How the WITH Clause Works
• GROUP BY vs HAVING in SQL: What's the Difference?
• 25 SQL Interview Questions and Answers