Preloader
Others
  • Estimated reading time: 13 Minutes

Window Functions in SQL: A Practical Guide With Examples

Window Functions in SQL: A Practical Guide With Examples

Window functions in SQL calculate a value for each row from a set of related rows, such as the other orders of the same customer, without collapsing them into one row. They answer questions GROUP BY cannot: the top three orders per customer, the change from last month, a running total, or each row's share of its group.

Key takeaways

  • A window function keeps every row and adds a calculated column. GROUP BY collapses rows into one per group.
  • OVER defines the window: PARTITION BY splits rows into groups, ORDER BY sets their order, and the frame picks which rows count.
  • Window functions run after WHERE, GROUP BY and HAVING, so filtering on one needs a CTE, or QUALIFY where the database has it.
  • In our benchmark on 200,000 rows, the window version of four common queries was 1.5 to 23 times faster than the older correlated subquery.

Contents

Part 1. The fundamentals

1.1 What is a window function in SQL?

1.2 How is a window function different from GROUP BY?

1.3 The sample table used in this guide

1.4 How does the OVER clause work?

1.5 Where do window functions run in a query?

1.6 How do you filter on a window function?

Part 2. The functions you will use most

2.1 Which window functions do analysts use most?

2.2 Top N per group with ROW_NUMBER

2.3 ROW_NUMBER, RANK and DENSE_RANK on ties

2.4 Month-over-month change with LAG

2.5 Share of a group's total

Part 3. Frames: ROWS, RANGE and GROUPS

3.1 How do frames work?

3.2 The default frame trap: running totals

3.3 The same default breaks LAST_VALUE

3.4 Moving averages

Part 4. Performance and common mistakes

4.1 Are window functions faster than the old way?

4.2 What mistakes do people make with window functions?

Frequently asked questions

Conclusion

Part 1. The fundamentals

1.1 What is a window function in SQL?

It is a function that looks at a "window" of rows related to the current row and returns a value for every row. The PostgreSQL documentation puts it this way: a window function performs a calculation across a set of table rows that are somehow related to the current row, but unlike an ordinary aggregate, the rows "retain their separate identities".

Window functions are widely supported. PostgreSQL has them, MySQL added them in version 8.0, and SQLite added them in version 3.25.0, released on 15 September 2018. SQL Server supports the OVER clause, with ROWS and RANGE frames from SQL Server 2012. These four were among the most used databases in the Stack Overflow Developer Survey 2025.

Usage from the Stack Overflow Developer Survey 2025; versions from each database's documentation.

1.2 How is a window function different from GROUP BY?

GROUP BY answers "one number per group". A window function answers "one number per row, in the context of its group". GROUP BY returns one row per group and lets you select only grouped or aggregated columns; a window function returns every input row and any column beside the result. You filter a GROUP BY result with HAVING, and a window result with an outer query or QUALIFY. Often you need both, and since window functions run after aggregation, one query can do both.

GROUP BY returns one row per group; a window function returns every row with the group's value beside it.

1.3 The sample table used in this guide

Every example uses one table, orders. Six rows are enough to check each result by hand:

CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER,
  order_date  DATE,
  amount      NUMERIC(10, 2)
);

INSERT INTO orders VALUES
  (1, 101, '2026-01-05', 120),
  (2, 101, '2026-01-05',  80),
  (3, 101, '2026-02-11', 260),
  (4, 102, '2026-01-20', 300),
  (5, 102, '2026-02-02', 300),
  (6, 102, '2026-03-15', 150);

Customer 101 placed two orders on the same day, and customer 102 has two orders of the same amount. Those ties are deliberate: they are where window functions surprise people.

1.4 How does the OVER clause work?

Every window function is followed by OVER, which describes the window. It has three parts, all optional:

  • PARTITION BY splits the rows into groups. The function restarts for each group.
  • ORDER BY sets the order of rows inside each group, which matters for ranking, running totals and looking at the previous row.
  • The frame picks which rows of the partition the function sees, such as "from the start up to this row".

Example:

SELECT
  order_id,
  customer_id,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

Every order keeps its own row, and each row also shows its customer's total: 460 for customer 101 and 750 for customer 102. With GROUP BY you would get two rows and lose the orders.

Only the function and OVER are required; leave out PARTITION BY and the whole result is one window.

1.5 Where do window functions run in a query?

Late. The PostgreSQL documentation says window functions are permitted only in the SELECT list and the ORDER BY clause, and they are evaluated after WHERE, GROUP BY and HAVING have done their work. BigQuery's documentation gives the same order: FROM, WHERE, GROUP BY and aggregation, HAVING, then window functions, then QUALIFY, then DISTINCT, ORDER BY and LIMIT. Two practical consequences follow:

  • You can window over an aggregate, for example ranking customers by SUM(amount) in one query.
  • You cannot filter on a window function in the same query's WHERE, because WHERE has already run.

Logical order, which is how to reason about a query; the planner may execute it differently.

That is the single most common window function error, and it is worth practising until it is automatic. If you are learning SQL for analysis and want someone checking your queries as you go, a data analytics program with live classes covers SQL alongside the reporting tools analysts use with it.

1.6 How do you filter on a window function?

Wrap the query and filter outside it, as in the top-N example in section 2.2. That works in every database. BigQuery also has a QUALIFY clause, which its documentation says "filters the results of window functions", so the same query needs no CTE:

-- BigQuery
SELECT order_id, customer_id, amount
FROM orders
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY customer_id
  ORDER BY order_date DESC, order_id DESC
) = 1;

Use QUALIFY where your database documents it, and the CTE everywhere else.

Part 2. The functions you will use most

2.1 Which window functions do analysts use most?

A short list covers most real work. PostgreSQL's built-in set is row_number, rank, dense_rank, percent_rank, cume_dist, ntile, lag, lead, first_value, last_value and nth_value, and any ordinary aggregate such as SUM or AVG also works with OVER.

Function What it returns A question it answers
ROW_NUMBER() 1, 2, 3 in order, no ties What is each customer's latest order?
RANK() Rank with gaps after ties Who are the top sellers, ties shared?
DENSE_RANK() Rank with no gaps after ties What are the top three distinct amounts?
LAG() / LEAD() The value from the previous or next row How much did this month change from last month?
SUM() OVER A total over the window What is the running total to date?
NTILE(n) The bucket number from 1 to n Which quarter of customers by spend is each in?

2.2 Top N per group with ROW_NUMBER

The classic use is picking the top rows for each group. Number the rows inside each customer by amount, then keep the first three.

WITH ranked AS (
  SELECT
    order_id,
    customer_id,
    amount,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY amount DESC, order_id   -- order_id breaks ties
    ) AS rn
  FROM orders
)
SELECT order_id, customer_id, amount
FROM ranked
WHERE rn <= 3;

The CTE is there because WHERE runs before window functions. The order_id tie-breaker matters too: orders 4 and 5 both have 300, and without it the database may number them either way from one run to the next.

2.3 ROW_NUMBER, RANK and DENSE_RANK on ties

All three number rows in order. They differ only when values tie. Sorted by amount, our six orders are 300, 300, 260, 150, 120 and 80:

SELECT
  order_id,
  amount,
  ROW_NUMBER() OVER (ORDER BY amount DESC, order_id) AS row_num,
  RANK()       OVER (ORDER BY amount DESC)           AS rnk,
  DENSE_RANK() OVER (ORDER BY amount DESC)           AS dense_rnk
FROM orders;

Use RANK when a tie should push later rows down, DENSE_RANK for the top distinct values, ROW_NUMBER when you need exactly one row each.

2.4 Month-over-month change with LAG

LAG returns a value from an earlier row in the window, which makes period-over-period comparisons a single query. Aggregate first, then compare:

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,  -- PostgreSQL
    SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT
  month,
  revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly;

DATE_TRUNC is PostgreSQL. In MySQL use DATE_FORMAT(order_date, '%Y-%m'), and in SQLite strftime('%Y-%m', order_date). The rest of the query is the same in all three.

 LEAD is the mirror image: it looks forward. Both take an optional offset and a default, as in LAG(revenue, 1, 0).

January has no previous row, so its change is NULL. LEAD does the same in the other direction.

2.5 Share of a group's total

Dividing a row by a windowed total gives each row's share of its group, with no join:

SELECT
  order_id,
  customer_id,
  amount,
  ROUND(
    100.0 * amount / SUM(amount) OVER (PARTITION BY customer_id), 1
  ) AS pct_of_customer
FROM orders;

Order 3 comes out at 56.5% of customer 101's 460.

Part 3. Frames: ROWS, RANGE and GROUPS

3.1 How do frames work?

The frame decides which rows of the partition a function like SUM, AVG or LAST_VALUE sees. PostgreSQL accepts three frame modes, ROWS, RANGE and GROUPS, and an optional EXCLUDE clause (CURRENT ROW, GROUP, TIES or NO OTHERS). SQLite has had all three modes and EXCLUDE since version 3.28.0.

  • ROWS counts physical rows: "the two rows before this one".
  • RANGE works on values of the ORDER BY column, so rows with equal values move together.
  • GROUPS counts groups of equal values, called peers.

3.2 The default frame trap: running totals

The trap is the default. When OVER has an ORDER BY and no frame, PostgreSQL's documentation describes the frame as running from the start of the partition up through the current row, "plus any following rows that are equal to the current row according to the ORDER BY clause". That is a RANGE frame, and ties make it jump.

SELECT
  order_id,
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) AS default_frame,
  SUM(amount) OVER (
    ORDER BY order_date, order_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS rows_frame
FROM orders
WHERE customer_id = 101;

With ties in the ORDER BY column, the default frame adds every tied row at once. Spell out ROWS for a true running total.

3.3 The same default breaks LAST_VALUE

Because the frame ends at the current row, or its last tie, LAST_VALUE returns that row's value rather than the partition's last. The PostgreSQL documentation calls this "likely to give unhelpful results for last_value". Give it the whole partition:

LAST_VALUE(amount) OVER (
  PARTITION BY customer_id
  ORDER BY order_date, order_id
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_amount

3.4 Moving averages

Frames also give you moving averages. On the monthly CTE from the LAG example, this averages each month with the two before it (three rows, so three months when no month is missing):

AVG(revenue) OVER (
  ORDER BY month
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3m

Part 4. Performance and common mistakes

4.1 Are window functions faster than the old way?

Before window functions, these queries were written with correlated subqueries: for each row, run another query over the same table. We timed four common patterns both ways on SQLite 3.45.1, with 200,000 orders across 5,000 customers and an index on (customer_id, order_date, order_id). Each figure is the median of five runs, and each pair returned exactly the same rows, checked with EXCEPT in both directions.

Query Window function Older form Older form, time Faster by
Running total per customer 318.5 ms Correlated SUM subquery 3,570.9 ms 11x
Top 3 orders per customer 262.2 ms Correlated COUNT subquery 6,037.1 ms 23x
Latest order per customer 124.5 ms NOT EXISTS 187.3 ms 1.5x
Change from previous month 394.9 ms Correlated subquery on the monthly totals 1,013.1 ms 2.6x

SQLite 3.45.1, 200,000 orders, median of five runs; each pair returned identical rows.

The gap is widest where the old form reads many rows again for every row, as in a running total or a top-N. It is narrow for "latest order", because an indexed NOT EXISTS is already cheap. This is one engine on one machine with synthetic data, so the exact ratios will differ on PostgreSQL, MySQL or SQL Server. Check your own case with EXPLAIN before and after.

4.2 What mistakes do people make with window functions?

The five errors behind most wrong window function results, and the one-line fix for each.

  1. Filtering in the same query. WHERE rn = 1 beside the ROW_NUMBER that creates rn fails. Use a CTE, or QUALIFY where it exists.
  2. Relying on the default frame. Running totals with tied ORDER BY values jump. Spell out ROWS.
  3. Forgetting ORDER BY inside OVER. ROW_NUMBER without it numbers rows in no guaranteed order.
  4. Ties in ROW_NUMBER. Which tied row gets 1 is arbitrary. Add a tie-breaker column.
  5. LAST_VALUE with the default frame. It returns the current row. Extend the frame to UNBOUNDED FOLLOWING.

Frequently asked questions

Why are window functions used in SQL?

They calculate across related rows while keeping every row in the result. That makes ranking, top-N per group, running totals, moving averages and period-over-period changes possible in one query, without self-joins or several aggregate queries stitched together. In our benchmark they were also faster than the correlated subqueries they replace.

What's the difference between RANK and DENSE_RANK?

Both give tied rows the same rank, but they differ after a tie. If two rows tie for first, RANK gives the next row 3, leaving a gap, while DENSE_RANK gives it 2. Use RANK when ties should push later rows down, and DENSE_RANK when you want consecutive values, such as the top three distinct amounts.

What are window functions in SQLite?

SQLite supports ROW_NUMBER, RANK, LAG, SUM OVER and the rest with the same OVER, PARTITION BY and ORDER BY syntax as other databases. Support arrived in version 3.25.0 in September 2018, and GROUPS frames and EXCLUDE in 3.28.0. The SQLite developers used PostgreSQL's window function documentation as their main reference, so queries usually carry across.

What are CTE and window functions?

A CTE, or common table expression, is a named subquery written with WITH that the rest of a query can use like a table. Window functions calculate across related rows with OVER. They work well together, because a window function cannot be filtered at the query level that creates it: compute it in a CTE, then filter the CTE.

How do you calculate a running total in SQL?

Use SUM with OVER and an ORDER BY, for example SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). The ROWS clause makes the total grow one row at a time. Without it, rows with the same order date are added together in one jump.

What are the 5 types of SQL?

SQL commands are usually grouped into five types: data query language (SELECT), data manipulation language (INSERT, UPDATE, DELETE), data definition language (CREATE, ALTER, DROP), data control language (GRANT, REVOKE) and transaction control language (COMMIT, ROLLBACK). Window functions belong to queries, inside SELECT.

Does MySQL support window functions?

Yes, from MySQL 8.0. Its reference manual documents ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD and NTILE, with the OVER and named WINDOW clauses. If a local setup rejects OVER, check the server version, because the examples in this guide need 8.0 or later.

Conclusion

Window functions turn questions that used to need self-joins into one readable query, and in our test they also ran faster. Learn ROW_NUMBER, RANK, LAG and SUM OVER first, always order inside OVER, add a tie-breaker, and spell out the frame for running totals and LAST_VALUE.

To keep building from here with exercises and feedback, start with the best SQL certification course online for beginners and work up to analytical queries like these.

Our Sponsors

Our blog is proudly supported by industry-leading sponsors.