SQL Window Functions: RANK, LAG, LEAD, ROW_NUMBER (2026)

Quick Answer: What Do Window Functions Do?

The Report GROUP BY Couldn't Build

HR asks for a report showing "each employee's salary, next to their department's average salary" — one row per employee. Someone reaches for the familiar tool: SELECT department, AVG(salary) FROM employees GROUP BY department. That query runs fine and returns a correct average — but it's one row per department, with every individual employee's name and salary gone. GROUP BY can't produce what was actually asked for, because collapsing rows is what GROUP BY fundamentally does; there's no way to keep individual employee rows while also summarizing at the department level in the same aggregation.

This is precisely the gap window functions fill: a calculation that spans a group of related rows, attached back onto every individual row instead of replacing them. The employee keeps their own row; the department average just becomes one more column on it.

Anatomy of OVER and PARTITION BY

Every window function shares the same basic shape: a function, followed by OVER (...) defining the window it operates across.

-- fixes the HR report from the hook: every employee, with their department's average alongside
SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees;

Every employee keeps their own row. PARTITION BY department tells the window function to calculate the average independently within each department, rather than across the whole table — an engineering department employee sees the engineering average; a sales department employee sees the sales average. Remove PARTITION BY entirely and the window function calculates across the whole result set as one single window instead.

ROW_NUMBER: A Unique Sequential Number

ROW_NUMBER() assigns a strictly increasing, unique integer to each row within its window, based on the ORDER BY inside OVER — no ties, ever, even when the ordering values are identical.

SELECT
  name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

Within each department, this numbers employees 1, 2, 3... by salary, highest first. If two employees in the same department have the exact same salary, ROW_NUMBER still assigns them different numbers — the specific tie-break order depends on the database when no further ORDER BY column is given, which is why ROW_NUMBER isn't the right choice when ties genuinely need to share a rank.

RANK vs DENSE_RANK: How Ties Are Handled

Both assign the same rank to tied rows — the difference is entirely in what happens to the rank number immediately after a tie.

SELECT
  name, salary,
  RANK() OVER (ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;

With two people tied for 2nd place, RANK() gives them both 2, then jumps straight to 4 for the next row — the "missing" 3 accounts for how many rows tied. DENSE_RANK() gives them both 2 as well, but continues at 3 with no gap. Which one is correct depends entirely on what the rank number needs to mean: "how many people are strictly ahead of me" (RANK) versus "how many distinct salary levels are ahead of me" (DENSE_RANK).

LAG and LEAD: Comparing to Another Row

LAG pulls a value from a previous row; LEAD pulls one from a following row — both relative to the ordering defined in the window, without needing a self-join.

-- month-over-month revenue change, no self-join required
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_revenue;

Before window functions, this kind of "compare to the previous row" logic typically required a self-join on a date offset — genuinely awkward SQL. LAG replaces that entirely: one line, no join. LEAD is the same idea in the opposite direction — pulling a value from a row that comes after the current one, useful for things like "days until the next order" or "next scheduled payment date."

Running Totals

Any aggregate function can be used as a window function — SUM() OVER (ORDER BY ...) with no PARTITION BY produces a running total by default.

SELECT
  order_date,
  order_total,
  SUM(order_total) OVER (ORDER BY order_date) AS running_total
FROM orders
ORDER BY order_date;

Without an explicit frame clause, SUM() OVER (ORDER BY ...) defaults to summing from the first row through the current row — exactly what a running total needs. This same pattern — an aggregate function combined with ORDER BY inside OVER — also produces running averages, running counts, and running min/max, all with the same basic shape.

Top N Per Group: The Single Most Useful Pattern

Combining ROW_NUMBER with PARTITION BY, then filtering on the result, answers "top N per group" — a question that's genuinely awkward to express without window functions.

-- the top 3 highest-paid employees in EACH department
WITH ranked AS (
  SELECT
    name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rn <= 3;

Window Functions vs GROUP BY, Side by Side

The two aren't competing tools — they answer structurally different questions. GROUP BY is correct when you genuinely want fewer rows, summarized. Window functions are correct when you need every original row preserved, with additional group-aware or row-relative context attached.

Mistakes That Give Away a Beginner

• Using GROUP BY when the individual rows are still needed — the exact trap from this article's opening. If any original row detail must survive, GROUP BY is the wrong tool.
• Trying to filter a window function directly in WHERE. It has to be wrapped in a CTE or subquery first, then filtered in the outer query.
• Confusing RANK and DENSE_RANK and getting an unexpected gap (or lack of one) after a tie.
• Forgetting PARTITION BY when a calculation should reset per group, causing it to run across the entire result set instead.
• Using ROW_NUMBER when ties should share a rank. ROW_NUMBER never ties — reach for RANK or DENSE_RANK when tied values genuinely need the same rank.

Practice: Write These Three Queries Yourself

Using an orders (order_id, customer_id, order_date, order_total) table, try writing these:

Frequently Asked Questions

Conclusion: Keep the Rows, Add the Context

Window functions solve a specific gap GROUP BY structurally can't: showing individual rows alongside group-level or row-relative context, without collapsing anything. ROW_NUMBER for a strict sequence, RANK or DENSE_RANK depending on how ties should be handled, LAG and LEAD for comparing a row to its neighbors, and any aggregate function with ORDER BY for a running calculation. Remember that filtering a window function's result always requires wrapping it in a CTE first — it can't be referenced directly in WHERE.

Practice the three queries above against real data, then move to the next article in this cluster, which covers query optimization — making queries like these run fast on tables with millions of rows.