# SQL Window Functions: The Complete Guide

A complete guide to SQL window functions — RANK, DENSE_RANK, ROW_NUMBER, LAG/LEAD, NTILE, and aggregate window functions, with frame clauses and performance notes explained.

- Author: SqlInt team
- Published: 2026-07-03T09:00:00+00:00
- Updated: 2026-09-08T10:11:41.599878+00:00
- Canonical: https://sqlint.com/articles/sql-window-functions-complete-guide
- Category: Window Functions & CTEs
- Tags: sql-window-functions, row-number, rank, lead-lag

---

## What Are Window Functions?

A window function performs a calculation across a set of related rows — a "window" — without collapsing them into a single output row the way GROUP BY does. Every input row keeps its own row in the result, but each one gets access to values from its neighbors: its rank within a group, the previous row's value, a running total, a group average sitting alongside the individual record. This is the single most tested advanced SQL topic in interviews, and the most useful one in real analytics work — ranking, trends, comparisons to a group, and period-over-period changes are almost always window function problems.

## Anatomy of a Window Function

Every window function follows the same shape: function() OVER (PARTITION BY ... ORDER BY ... frame_clause).

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

  - The function — what to compute: SUM, AVG, RANK, LAG, and so on

  - PARTITION BY — optional; divides rows into independent groups, similar to GROUP BY, but without merging rows

  - ORDER BY — required for ranking and offset functions; defines the order the calculation moves through each partition

  - Frame clause — optional; narrows the window to a specific slice of rows around the current one (covered below)

Without PARTITION BY, the entire result set is treated as one window.

## Ranking Functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE

SELECT
  name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS drnk,
  NTILE(4)     OVER (PARTITION BY department ORDER BY salary DESC) AS quartile
FROM employees;

  - ROW_NUMBER() — unique sequential numbers regardless of ties (1, 2, 3, 4)

  - RANK() — ties share a rank, next rank skips ahead (1, 1, 3, 4)

  - DENSE_RANK() — ties share a rank, no gap afterward (1, 1, 2, 3)

  - NTILE(n) — splits rows into n roughly equal buckets, commonly used for quartiles/deciles in analytics (e.g. "top 25% of customers by spend")

Choosing wrong between these three ranking functions is the single most common window-function mistake — see our dedicated breakdown of when each one is correct in the Nth highest salary guide.

## Offset Functions: LAG and LEAD

LAG() and LEAD() pull a value from a previous or following row without a self join — extremely common for period-over-period comparisons.

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change,
  LEAD(revenue) OVER (ORDER BY month) AS next_month_revenue
FROM monthly_revenue;

Both accept an optional second argument for how many rows back/forward to look (default 1), and an optional third argument as the default value to use when there is no such row (instead of NULL) — e.g. LAG(revenue, 1, 0).

## Aggregate Window Functions

Any standard aggregate — SUM, AVG, COUNT, MIN, MAX — can be used as a window function simply by adding OVER (...). This is the mechanism behind running totals and group-relative comparisons.

SELECT
  order_date, amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total,
  COUNT(*) OVER (PARTITION BY customer_id) AS total_orders_by_customer
FROM orders;

For a full deep dive on running totals and moving averages specifically, see our dedicated guide.

## FIRST_VALUE, LAST_VALUE, and NTH_VALUE

SELECT
  name, department, salary,
  FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS top_earner,
  LAST_VALUE(name) OVER (
    PARTITION BY department ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS lowest_earner
FROM employees;

The LAST_VALUE trap: without an explicit frame clause, most databases default the window to "start of partition through the current row," which means LAST_VALUE just returns the current row's own value — not the actual last row of the partition. You almost always need ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the behavior people expect, and this exact gotcha is a favorite "gotcha" interview question.

## Understanding the Frame Clause: ROWS vs RANGE

The frame clause controls exactly which rows, relative to the current row, participate in the calculation.

-- 7-day moving average using a ROWS frame (physical row count)
SELECT
  order_date, revenue,
  AVG(revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_revenue;

  - ROWS — counts physical rows, regardless of whether their values tie. Use this for "the last N rows" logic like a fixed 7-row moving average.

  - RANGE — groups by logical value, treating tied ORDER BY values as part of the same frame step. Rarely what you want for row-count-based windows; it matters mainly with duplicate ORDER BY values.

  - Default frame (when ORDER BY is present but no frame is specified) is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — this is why running totals work "by default" but N-row moving averages require an explicit ROWS BETWEEN clause.

## Window Functions vs. GROUP BY

GROUP BYWindow Function
Collapses rows into one per groupKeeps every original row
Can't reference ungrouped columnsFull row detail stays available alongside the calculation
Filtered post-aggregation with HAVINGFiltered by wrapping in a subquery/CTE and filtering the computed column

A common pattern is needing both — the group aggregate and the row detail on the same line — which only a window function can do in a single pass; the equivalent with GROUP BY would require a self join back to the ungrouped data.

## Performance Notes

  - Window functions require sorting the data per partition, so an index that already matches the PARTITION BY + ORDER BY columns can let the engine skip an explicit sort step.

  - Multiple window functions in the same query that share the same PARTITION BY/ORDER BY are typically computed in a single pass by the optimizer — write them with matching window definitions where possible rather than needlessly varying the clause.

  - Filtering on a window function's result always requires wrapping it in a subquery or CTE, since WHERE can't reference window function aliases directly (window functions logically execute after WHERE).

-- This does NOT work — WHERE can't see window function results
SELECT name, RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
WHERE rnk = 1;  -- ERROR

-- Correct: filter in an outer query
SELECT * FROM (
  SELECT name, RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 1;

## Common Interview Questions

  - What's the difference between ROW_NUMBER, RANK, and DENSE_RANK? Covered above — the key distinction is how each handles ties.

  - Why can't you filter directly on a window function in WHERE? Window functions are logically evaluated after WHERE and GROUP BY, so their results aren't yet available to filter on in the same query level — you need a subquery or CTE.

  - How would you calculate each employee's salary as a percentage of their department's total? salary / SUM(salary) OVER (PARTITION BY department).

  - What's the difference between LAG and a self join for comparing consecutive rows? LAG avoids the join entirely, is more concise, and doesn't risk the row-multiplication mistakes a self join can introduce.

## Frequently Asked Questions

### Do window functions work on every SQL database?

Yes, in modern versions — PostgreSQL, MySQL 8.0+, SQL Server, Oracle, and SQLite (3.25+) all support the standard window function syntax described here. Older MySQL (pre-8.0) does not.

### Are window functions slower than GROUP BY?

Not inherently — both require sorting or hashing the data. Window functions are actually often faster than the equivalent GROUP BY + self join pattern needed to achieve the same "detail row plus aggregate" result.

## Practice Window Functions

Window functions reward hands-on repetition more than memorized syntax. Try them with our SQL practice questions, or apply them to a full business scenario in our case studies. For related patterns, see our guides on running totals and moving averages and top N per group.

---

Source: https://sqlint.com/articles/sql-window-functions-complete-guide