SQL Window Functions: The Complete Guide

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 BY	Window Function
Collapses rows into one per group	Keeps every original row
Can't reference ungrouped columns	Full row detail stays available alongside the calculation
Filtered post-aggregation with HAVING	Filtered 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

1. What's the difference between ROW_NUMBER, RANK, and DENSE_RANK? Covered above — the key distinction is how each handles ties.
2. 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.
3. How would you calculate each employee's salary as a percentage of their department's total? salary / SUM(salary) OVER (PARTITION BY department).
4. 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.