A window function is an SQL function that computes a value for each row from a set of related rows, called the window, while keeping every row in the result. Unlike an aggregate used with GROUP BY, which collapses many rows into one, a window function adds a calculated column such as a running total, a rank or the previous row’s value.
Window functions are identified by the OVER clause, which defines how rows are partitioned, ordered and framed. They were standardized in SQL:2003 and are supported by PostgreSQL, SQL Server, Oracle Database, MySQL 8.0 and later, MariaDB 10.2 and later, SQLite 3.25 and later, and most analytical engines.
How the OVER clause defines a window
The OVER clause has three optional parts, and together they decide which rows each calculation sees. An empty OVER () treats the whole result set as one window.
| Part | Role | Example |
|---|---|---|
| PARTITION BY | Splits rows into independent groups | PARTITION BY customer_id |
| ORDER BY | Orders rows inside each partition | ORDER BY order_date |
| Frame clause | Limits rows relative to the current row | ROWS BETWEEN 6 PRECEDING AND CURRENT ROW |
PARTITION BY resembles GROUP BY, but it does not merge rows: each row stays visible and receives a result computed only from rows in its own partition. ORDER BY inside OVER is independent of the query’s final ORDER BY; it controls ranking order and the direction of running calculations.
Window functions are evaluated after WHERE, GROUP BY and HAVING but before the final ORDER BY and LIMIT. That is why a window result cannot be filtered in WHERE of the same query; the usual solution is to wrap the query in a subquery or CTE and filter in the outer level. Some engines, such as Snowflake, DuckDB and BigQuery, add a QUALIFY clause for this purpose.
Families of window functions
Window functions fall into three families: ranking functions, offset or value functions, and ordinary aggregates used with OVER. Each answers a different kind of analytical question.
| Function | Family | Returns |
|---|---|---|
| ROW_NUMBER() | Ranking | Unique sequence 1, 2, 3 even for ties |
| RANK() | Ranking | Ties share a rank, gaps follow: 1, 1, 3 |
| DENSE_RANK() | Ranking | Ties share a rank, no gaps: 1, 1, 2 |
| NTILE(n) | Ranking | Bucket number from 1 to n |
| LAG() and LEAD() | Offset | Value from a previous or following row |
| FIRST_VALUE(), LAST_VALUE() | Offset | Value from the frame’s first or last row |
| SUM(), AVG(), COUNT() | Aggregate | Running or partition-wide totals |
The choice among ranking functions matters for ties. ROW_NUMBER is right for deduplication, where exactly one row per group must win; RANK and DENSE_RANK are right for leaderboards, where equal scores deserve equal places. Because ROW_NUMBER breaks ties arbitrarily, it should be given a deterministic ORDER BY that includes a unique column.
Frames: ROWS, RANGE and GROUPS
The window frame defines which rows around the current row an aggregate or value function sees. Frames matter only for functions that use them, such as SUM, AVG and LAST_VALUE; ranking functions ignore them.
| Frame unit | Counts by | Typical use |
|---|---|---|
| ROWS | Physical row positions | Moving average over the last 7 rows |
| RANGE | Value distance in ORDER BY | All rows within 30 days of the current date |
| GROUPS | Groups of peer rows | Previous two distinct values with their ties |
The most common surprise is the default frame. When ORDER BY is present and no frame is given, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This produces a running total, but rows with equal ORDER BY values are peers and all receive the same cumulative sum. It also explains why LAST_VALUE often returns the current row instead of the partition’s last row; the fix is an explicit frame ending in UNBOUNDED FOLLOWING.
Window functions compared with GROUP BY, joins and subqueries
Before window functions, the same results required self-joins or correlated subqueries, which were longer to write and often slower. Window functions let one pass over sorted data answer per-row questions about neighboring rows.
| Need | Older technique | Window approach |
|---|---|---|
| Total per group, keep detail rows | JOIN to a GROUP BY subquery | SUM(x) OVER (PARTITION BY g) |
| Previous row’s value | Self-join on row number minus one | LAG(x) OVER (ORDER BY t) |
| Top N per group | Correlated subquery with count | ROW_NUMBER in a CTE, then filter |
| Running total | Correlated SUM per row | SUM(x) OVER (ORDER BY t) |
GROUP BY remains the right tool when the result should have one row per group. Window functions are the right tool when the result should keep one row per input row with extra context attached.
Performance and common pitfalls
A window function typically requires the engine to sort each partition, which dominates the cost on large tables. An index whose leading columns match the PARTITION BY and ORDER BY columns can let the engine skip that sort.
- Several window functions with the same OVER definition share one sort; a named WINDOW clause makes this explicit.
- Different OVER definitions in one query may force multiple sorts, which is costly on millions of rows.
- Filtering by a window result requires an outer query, because WHERE runs before window evaluation.
- Missing ORDER BY inside OVER makes ROW_NUMBER and LAG nondeterministic across executions.
- Engines that spill large partitions to disk can slow down sharply when one partition is heavily skewed.
A non-technical view
A window function lets a database look at neighboring rows without losing any row. Imagine a printed bank statement. Next to each transaction you write the running balance, the size of the previous payment, or whether this was the largest payment of the month.
Every line of the statement stays in place; you only add a new column of notes. That added column is what a window function produces, and the database fills it in for millions of lines in one step.
A concrete example
An analyst at an online retailer needs each customer’s most recent order plus the number of days since the order before it. The orders table holds about 20 million rows. She writes a CTE that adds ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, order_id DESC) and LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date).
The outer query keeps only rows where the row number equals 1 and subtracts the lagged date from the order date. The previous version of the report used two self-joins and a correlated subquery. After adding an index on customer_id and order_date, the window-based query returns in seconds instead of several minutes, and its logic fits in one readable statement that colleagues can review.