Querying
Window Functions
Window functions compute a value for each row based on a set of related rows, called a window or partition, without collapsing those rows into a single.
Overview
Window functions compute a value for each row based on a set of related rows, called a window or partition, without collapsing those rows into a single output row. Unlike aggregate functions used with GROUP BY, which reduce many input rows to one row per group, a window function returns one value per input row while still allowing access to the surrounding rows. This makes window functions useful for running totals, moving averages, rankings, percentiles, lag and lead comparisons, and gap analysis. The feature was introduced in SQL:2003 and is now supported by most major relational database systems, though syntax and function coverage vary. A window is defined by an OVER clause that may include PARTITION BY, ORDER BY, and a frame specification. The window is evaluated after FROM, WHERE, GROUP BY, and HAVING, but before ORDER BY and LIMIT in the logical processing order. Because the result set is not reduced, window functions can appear in the SELECT list and in ORDER BY, but not in WHERE, GROUP BY, or HAVING clauses.
Syntax and usage
The general form is function_name(...) OVER ( [PARTITION BY ...] [ORDER BY ...] [frame_clause] ). The function may be an aggregate such as SUM, AVG, COUNT, MIN, or MAX, a ranking function such as ROW_NUMBER, RANK, DENSE_RANK, NTILE, or PERCENT_RANK, or a value function such as LAG, LEAD, FIRST_VALUE, LAST_VALUE, or NTH_VALUE. PARTITION BY splits rows into independent groups; if omitted, the whole result set is one partition. ORDER BY defines the order within each partition and is required for ranking and for frame-based calculations. The frame clause, written as ROWS, RANGE, or GROUPS between two boundaries, controls which rows are visible to the function. Common frames include ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for running totals and ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING for moving windows. Named windows via the WINDOW clause can reduce repetition. Vendor differences exist: some systems allow a frame only with ORDER BY, and some restrict which functions accept frames.
How it works
Conceptually, the database partitions the rows produced by the preceding query operations, sorts each partition according to the window ORDER BY, and then evaluates the function once per row over the rows in that row's frame. The frame is defined relative to the current row and can be unbounded, a fixed number of rows, or a range of values. For ranking functions, the frame is ignored and the function considers the entire partition. For aggregate and value functions, the frame determines which rows contribute. The default frame, when ORDER BY is present, is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes all rows from the start of the partition through the current row and any peers with equal ordering values. Without ORDER BY, the default frame is the entire partition. Because window functions are evaluated after grouping, they can reference grouped results but cannot filter them directly. Multiple window functions in one query may use different windows, and the optimizer may reuse a single sort or partition pass when windows are compatible. Performance depends on partitioning, sorting, and the amount of data retained per frame.
Examples
A running total per customer: SELECT customer_id, order_date, amount, SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM orders. Ranking rows within each department: SELECT employee_id, department_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank FROM employees. Comparing each row to the previous one: SELECT trade_date, price, LAG(price) OVER (ORDER BY trade_date) AS prev_price, price - LAG(price) OVER (ORDER BY trade_date) AS change FROM prices. A moving average over three rows: SELECT day, sales, AVG(sales) OVER (ORDER BY day ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg FROM daily_sales. To keep only the latest row per group, wrap the window in a subquery or common table expression and filter on the rank: SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn FROM orders) t WHERE rn = 1. These examples use standard syntax supported by most systems.
Common pitfalls
A frequent mistake is expecting the default frame to cover the whole partition when ORDER BY is present; the default is cumulative through the current row, which changes running totals and moving calculations. Another is using window functions in WHERE, GROUP BY, or HAVING, where they are not allowed because they are evaluated later; wrap them in a subquery or CTE instead. Mixing PARTITION BY with an ORDER BY that does not uniquely identify rows can produce nondeterministic results for ranking functions, since ties are broken arbitrarily. LAST_VALUE often surprises users because the default frame ends at the current row; use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to see the partition end. RANGE frames with numeric or date offsets behave differently from ROWS frames and may include peer rows. Null handling in ORDER BY can also affect ranking and lag results. Finally, large partitions and wide frames can increase memory and sort costs, so test performance on realistic data volumes.
Best practices
Use PARTITION BY to scope calculations to meaningful groups and ORDER BY to define a deterministic sequence, adding tie-breaker columns when needed. Prefer explicit frame clauses over relying on defaults, especially for running totals and moving windows, so intent is clear and behavior is portable. Use named windows with the WINDOW clause when several functions share the same window definition. Filter rows before the window when possible, since WHERE runs earlier and reduces the data the window must process. For top-n-per-group problems, compute a rank in a subquery or CTE and filter in the outer query. Choose ROWS over RANGE unless value-based peer grouping is required. Keep window functions out of WHERE, GROUP BY, and HAVING. When performance matters, check that the optimizer can reuse sorts and that partitioning columns align with indexes. Document assumptions about ordering and ties, because results depend on them.
Frequently asked questions
What is the difference between a window function and an aggregate function with GROUP BY? An aggregate with GROUP BY returns one row per group and collapses detail rows. A window function returns one value per input row and preserves the detail, using the OVER clause to define the related rows. Can I use a window function in a WHERE clause? No. Window functions are evaluated after WHERE, GROUP BY, and HAVING, so they cannot appear there. Compute the window value in a subquery or CTE, then filter on it in the outer query. Why does LAST_VALUE return the current row instead of the last row of the partition? Because the default frame ends at the current row when ORDER BY is present. Specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to include the entire partition. Are window functions supported in all databases? Most major systems support them, but function coverage, frame syntax, and named windows vary, so verify behavior for your target system.