Window Functions in SQL for Data Analysis

A window function calculates a value for each row from a group of related rows, and keeps every row in the result. For example, you can list every order of an orders table with its customer's running total beside it.

Every window function has the same shape:

function(...) OVER (
  PARTITION BY column
  ORDER BY column
  ROWS BETWEEN start AND end
)

• function is an aggregate such as SUM or AVG, or a function that exists only for windows, such as ROW_NUMBER, RANK, or LAG.
• OVER turns the function into a window function. Every part inside the parentheses of OVER is optional.
• PARTITION BY splits the rows into groups, called partitions. The function starts again in each partition.
• ORDER BY sorts the rows inside each partition.
• ROWS BETWEEN sets the frame: the rows of the partition that the function reads for each row.

Create a sample table of six orders:

CREATE TABLE orders (
  customer   text,
  order_date date,
  amount     int
);

INSERT INTO orders VALUES
  ('Anna', '2026-03-01', 100),
  ('Anna', '2026-03-05', 300),
  ('Anna', '2026-03-09', 300),
  ('Ben',  '2026-03-02', 200),
  ('Ben',  '2026-03-06', 500),
  ('Ben',  '2026-03-08', 100);

Every statement in this article was run on PostgreSQL 18, MySQL 9.1, and SQLite 3.50, and returned the same rows on all three.

Window functions and GROUP BY

GROUP BY returns one row per group. A window function returns every row, with the calculation in one more column.

SELECT customer, sum(amount) AS customer_total
FROM orders
GROUP BY customer
ORDER BY customer;

The same SUM with OVER keeps all six orders:

SELECT customer, order_date, amount,
       sum(amount) OVER (PARTITION BY customer) AS customer_total
FROM orders
ORDER BY customer, order_date;

PARTITION BY customer made one partition for Anna and one for Ben, and SUM ran once in each. Anna's three orders each show 700, and Ben's show 800. You can now compare each order's amount with its customer's total in the same row.

ORDER BY and running totals

With ORDER BY inside OVER, SUM adds each row's amount to the sum of the rows before it. The result is a running total, such as each customer's amounts added up by date.

SELECT customer, order_date, amount,
       sum(amount) OVER (PARTITION BY customer ORDER BY order_date) AS running_total
FROM orders
ORDER BY customer, order_date;

Anna's total grows from 100 to 400 to 700. The total starts again at 200 for Ben, because Ben's orders are another partition.

ROW_NUMBER

ROW_NUMBER numbers the rows of each partition from 1, in the order that ORDER BY gives. For example, you can number each customer's orders by date.

SELECT customer, order_date, amount,
       row_number() OVER (PARTITION BY customer ORDER BY order_date) AS order_no
FROM orders
ORDER BY customer, order_date;

Anna's first order gets 1, and her last order gets 3. The numbering starts again at 1 for Ben. ROW_NUMBER takes no argument.

RANK and DENSE_RANK

RANK and DENSE_RANK number the rows like ROW_NUMBER, but tied rows, with the same ORDER BY value, get the same number. For example, you can rank each customer's orders by amount, from largest to smallest. The two functions differ in the number that follows a tie.

SELECT customer, amount,
       rank()       OVER (PARTITION BY customer ORDER BY amount DESC) AS amount_rank,
       dense_rank() OVER (PARTITION BY customer ORDER BY amount DESC) AS amount_dense_rank
FROM orders
ORDER BY customer, amount DESC;

Anna has two orders of 300, and both rank first. For her order of 100, RANK jumps to 3 because two rows come first, and DENSE_RANK continues with 2. Ben has no tie, so the two functions agree on his rows.

LAG and LEAD

LAG returns a column's value from the previous row of the partition. LEAD returns the column's value from the next row. For example, you can show each order with the amount of the same customer's previous order.

SELECT customer, order_date, amount,
       lag(amount) OVER (PARTITION BY customer ORDER BY order_date) AS previous_amount
FROM orders
ORDER BY customer, order_date;

Anna's second order shows 100, the amount of her first order. A customer's first order has no previous row, so LAG returns NULL there. Replace lag with lead, and each row shows the next order's amount, with NULL on each customer's last order.

ROWS BETWEEN

ROWS BETWEEN sets the first and the last row of the frame. For example, with 1 PRECEDING AND CURRENT ROW, SUM adds each order's amount to the amount of the order before it.

SELECT customer, order_date, amount,
       sum(amount) OVER (PARTITION BY customer ORDER BY order_date
                         ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS last_two
FROM orders
ORDER BY customer, order_date;

Anna's last order shows 600, the sum of her last two orders, instead of her running total of 700. A customer's first order has no row before it, so it shows only its own amount.

Window functions in WHERE

A window function can't appear in WHERE. PostgreSQL rejects such a query with the error "window functions are not allowed in WHERE". PostgreSQL filters the rows with WHERE first, and calculates the window functions after that.

Calculate the window function in a subquery, and filter on the window function's column in the outer query. For example, this query keeps each customer's latest order:

SELECT customer, order_date, amount
FROM (
  SELECT customer, order_date, amount,
         row_number() OVER (PARTITION BY customer ORDER BY order_date DESC) AS rn
  FROM orders
) numbered
WHERE rn = 1
ORDER BY customer;

With ORDER BY order_date DESC, row_number gives 1 to each customer's latest order. The outer WHERE keeps the two rows numbered 1. Change the filter to rn <= 2, and the query returns each customer's two latest orders.

What goes wrong

Ties in a running total

With ORDER BY and no ROWS BETWEEN, the frame runs from the start of the partition to the current row. This default frame also includes every row with the same ORDER BY value as the current row. Sort the running total by amount, and Anna's two orders of 300 tie:

SELECT customer, amount,
       sum(amount) OVER (PARTITION BY customer ORDER BY amount) AS running_total
FROM orders
WHERE customer = 'Anna'
ORDER BY amount;

The frame of each order of 300 holds both orders of 300, so both show 700. UNBOUNDED PRECEDING names the first row of the partition. Add ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW after the ORDER BY, and the two rows show 400 and 700.

LAST_VALUE returns the current row

LAST_VALUE returns a value from the last row of the frame. The default frame ends at the current row, so LAST_VALUE returns the current row's own value:

SELECT customer, order_date, amount,
       last_value(amount) OVER (PARTITION BY customer ORDER BY order_date) AS last_amount
FROM orders
WHERE customer = 'Ben'
ORDER BY order_date;

Every row shows its own amount as last_amount. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING after the ORDER BY, and the frame is the whole partition. All three rows then show 100, the amount of Ben's last order.

Run the queries in DbSchema

The SQL Editor in DbSchema runs a query and shows its rows in a grid under the editor. Press Editor in the toolbar, choose SQL Editor, paste a query from this article, and press Run.

Download DbSchema at https://dbschema.com/download.html and connect it to PostgreSQL, MySQL, or SQLite. Paste the CREATE TABLE and INSERT statements into the SQL Editor, then run each query and change it. The free Community Edition includes the database connection and the SQL Editor.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download