SQL Window Functions: ROW_NUMBER, RANK & PARTITION BY

A window function computes a value for each row from a set of related rows — its window — 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. That one idea powers rankings, running totals, comparisons with the previous row, and the top-N-per-group questions that come up in many SQL interviews.

Every example below runs in the playground, which uses SQLite, and shows the rows it returns. The window syntax itself — OVER, PARTITION BY, the ORDER BY inside it and the frame clauses used here — is standard SQL and works the same way in PostgreSQL, MySQL 8+, SQL Server and Oracle.

Numbering rows: ROW_NUMBER ▶ runs here

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

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.

Notice the two ORDER BYs, because they do different jobs. The one inside OVER decides which row gets which number; the one at the end decides the order the rows are shown in. Leave the outer one out and the database may return the rows in any order, numbers and all.

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
ORDER BY dept_id, salary DESC;

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: every Engineering row (dept 10) carries 99200, every Sales row (dept 20) 100000. 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 ▶ runs here

The three numbering functions differ only in how they treat ties, and no two employees earn the same, so this example ranks products by stock level instead: three of them share the top figure of 999.

SELECT name, in_stock,
       ROW_NUMBER() OVER (ORDER BY in_stock DESC, name) AS row_num,
       RANK()       OVER (ORDER BY in_stock DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY in_stock DESC) AS dense_rnk
FROM Products
ORDER BY in_stock DESC, name;

RANK gives the three tied products 1 and then skips: Quill Keyboard, fourth in line, gets 4. DENSE_RANK also gives the three 1 but does not skip, so Quill Keyboard gets 2. ROW_NUMBER never ties — the three get 1, 2 and 3 — and which of them gets which is up to the database unless you add a tie-breaker. name is the tie-breaker here, which is why those three are numbered alphabetically. Interviewers often put the difference as "1, 2, 2, 4 versus 1, 2, 2, 3"; the table is the same idea with real rows.

The interview classic: top earner per department ▶ runs here

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
ORDER BY dept_id;

"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. The ranking has to happen in a CTE or subquery because WHERE is evaluated before window functions, so rn does not exist yet at that point. Four rows come back: Support (dept 40) has no employees, so it has no partition at all. Change rn = 1 to rn <= 3 and it is top three per department.

Top N per group when there are ties ▶ runs here

ROW_NUMBER is the right tool only when you want exactly N rows per group. If two rows tie at the cut-off, it keeps one and silently drops the other. Here is "the customer with the most orders in each tier", ranked with RANK instead:

WITH ranked AS (
  SELECT c.tier, c.company, COUNT(*) AS orders,
         RANK() OVER (PARTITION BY c.tier ORDER BY COUNT(*) DESC) AS rnk
  FROM Customers c
  JOIN Orders o ON o.customer_id = c.customer_id
  GROUP BY c.customer_id, c.tier, c.company
)
SELECT tier, company, orders
FROM ranked
WHERE rnk = 1
ORDER BY tier, company;

Five rows for four tiers: Acme Robotics and Fjord Analytics placed two orders each, the most in Platinum, so both get rank 1 and both are kept. Swap RANK for ROW_NUMBER and Platinum shows only one of them, with nothing in the query to say which. So choose on purpose: ROW_NUMBER for exactly N rows, RANK to keep everyone tied at the cut-off (which can mean more than N rows), DENSE_RANK for the top N distinct values.

The query also shows the order of evaluation. GROUP BY runs before window functions, which is why RANK can order by COUNT(*), a value that only exists once the rows have been grouped.

Running totals and moving averages ▶ runs here

An aggregate with an ORDER BY inside OVER accumulates, so SUM(order_total) OVER (ORDER BY order_date) is a running total. Add a frame clause and AVG becomes a moving average:

SELECT order_date, order_total,
       SUM(order_total) OVER (ORDER BY order_date) AS running_total,
       ROUND(AVG(order_total) OVER (ORDER BY order_date
             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS avg_last_3
FROM Orders
ORDER BY order_date;

Each running_total is the sum of every order up to and including that one, so the last row, 78651.31, is all fifteen orders together (cancelled ones included; add a WHERE to leave them out). This is the cumulative-revenue line every dashboard has, in one expression.

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is a frame of at most three rows: this order and the two before it. At the start there are fewer rows to average, so the first avg_last_3 is just the first order's 15588 and the second is the mean of two orders, 9142.5. The opening values of any moving average rest on less data than the rest, which is worth saying on a chart.

One subtlety. When OVER has an ORDER BY but no frame, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE treats rows with the same order_date as one step: two orders on the same day would both show a total that includes both. Every order date in the sample is different, so it makes no difference here. Write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when you want a strict row-by-row total.

Comparing with the previous row: LAG and LEAD ▶ runs here

LAG reads a value from an earlier row of the window, LEAD from a later one. The classic use is "how much did this change since last time?" Here is each order beside the same customer's previous order, for three customers so the table stays short:

SELECT customer_id, order_date, order_total,
       LAG(order_total) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_total,
       order_total - LAG(order_total) OVER (PARTITION BY customer_id ORDER BY order_date) AS diff
FROM Orders
WHERE customer_id IN (101, 102, 105)
ORDER BY customer_id, order_date;

PARTITION BY keeps each customer's history separate, so every customer's first order has no previous row: LAG returns NULL there, and so does diff, because anything minus NULL is NULL. Customer 101 went from 15588 to 4760, a change of -10828. Write LAG(order_total, 1, 0) to get 0 instead of NULL on those first rows; the 1 is how many rows back to look. LEAD takes the same arguments, looks forward, and returns NULL on each customer's last order.

WHERE runs before the window function, so LAG only ever sees the rows that survive it. Run the same pattern over monthly totals and you have month-over-month change; the SQL for data analysts guide builds exactly that.

Splitting rows into buckets: NTILE ▶ runs here

NTILE(n) deals the rows, in window order, into n numbered groups of as equal a size as possible — the tool for quartiles, deciles and price bands:

SELECT name, unit_price,
       NTILE(3) OVER (ORDER BY unit_price DESC) AS price_band
FROM Products
ORDER BY unit_price DESC;

Seven products do not divide evenly into three, so the bands hold three, two and two: when the rows do not split exactly, the earlier buckets get one extra row each. NTILE looks only at position, not at value, so two rows with the same value can land in different buckets. Check for ties before you label a bucket "the top third".

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

• How to Find the Second Highest Salary in SQL (5 Ways)
• SQL CTEs Explained: How the WITH Clause Works
• GROUP BY vs HAVING in SQL: What's the Difference?
• 25 SQL Interview Questions and Answers
• SQL Scenario Questions for Data Analyst Interviews
• SQL for Data Analysts: The Queries You Actually Write