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
nroughly 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 explicitROWS BETWEENclause.
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 BYcolumns can let the engine skip an explicit sort step. - Multiple window functions in the same query that share the same
PARTITION BY/ORDER BYare 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
WHEREcan't reference window function aliases directly (window functions logically execute afterWHERE).
-- 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.