What Are Window Functions?

A window function computes a value across a set of rows — a window — without collapsing those rows into groups. GROUP BY summarises and reduces the row count; a window function summarises and keeps every row, adding the summary alongside the row's own values.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (id, customer_id, amount, placed_on) VALUES
  (1, 1, 120.00, '2026-01-05'),
  (2, 1, 340.50, '2026-01-20'),
  (3, 2,  75.25, '2026-02-03'),
  (4, 1,  90.00, '2026-02-14');

SELECT id, customer_id, amount,
       SUM(amount) OVER (PARTITION BY customer_id) AS customer_total,
       SUM(amount) OVER () AS grand_total
FROM orders
ORDER BY customer_id, id;

Four rows come back, and every one of them is still an individual order. Ada's three orders each carry her customer total of 550.50; Grace's single order carries 75.25. All four carry the grand total of 625.75, which is the same value in every row.

The OVER clause is what makes it a window function, and it has three parts. PARTITION BY divides the rows into separate windows — without it, every row sees every other row. ORDER BY defines the order within the window, which matters for the order-sensitive functions like ROW_NUMBER, RANK, LAG and running totals. The frame specification, which you can omit for the defaults, defines exactly which rows within the partition are included.

The comparison with GROUP BY is the thing to have ready for an interview. With GROUP BY you write SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id and get one row per customer. With the window function you write SUM(amount) OVER (PARTITION BY customer_id) and get every order, each decorated with its customer's total. When you want to reduce, use GROUP BY; when you want to annotate, use a window.

Two window functions over the same partition, one ordered by date and one with an explicit frame for the running average.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (id, customer_id, amount, placed_on) VALUES
  (1, 1, 120.00, '2026-01-05'),
  (2, 1, 340.50, '2026-01-20'),
  (3, 2,  75.25, '2026-02-03'),
  (4, 1,  90.00, '2026-02-14');

SELECT id,
       customer_id,
       amount,
       ROW_NUMBER() OVER (
         PARTITION BY customer_id
         ORDER BY placed_on
       ) AS order_seq,
       ROUND(AVG(amount) OVER (
         PARTITION BY customer_id
         ORDER BY placed_on
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ), 2) AS running_avg
FROM orders
ORDER BY customer_id, placed_on;

Four rows come back. Ada's orders are numbered 1, 2 and 3, with running averages of 120.00, 230.25 and 183.50. Grace has one order, numbered 1, with a running average of 75.25.

Two details make this work. The PARTITION BY resets the sequence for each customer, so Ada's numbering starts at 1 and Grace's restarts too. And the frame specification on the average — ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — says explicitly that the average is over everything from the start of the partition up to and including the current row. Without the frame, the default is the whole partition, which would give every one of Ada's rows the same average of 183.50 rather than a running one.

The tie-breaker matters too. If two of Ada's orders had the same date, the ordering would be ambiguous and the numbering could differ between runs, so adding a unique column to the ORDER BY inside the window — ORDER BY placed_on, id — makes the result deterministic. That is worth doing on every window function that produces a position or a rank.

A frame is the precise set of rows within the partition that an order-sensitive window function looks at. It is defined by a start point, an end point, and a unit of rows.

There are two units. ROWS counts physical rows, so it treats every row as distinct. RANGE counts logical rows by their value, so rows that tie on the ORDER BY expression are peers and are always included or excluded together. The difference only shows up when there are ties, and it shows up a lot.

The default frame depends on the function. For the aggregate functions — SUM, AVG, COUNT, MIN, MAX — and for the ranking functions, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when the window has an ORDER BY, which means the whole partition up to the current row, with tied rows treated as peers. Without an ORDER BY, the default is the entire partition. For ROW_NUMBER, RANK and DENSE_RANK the frame is irrelevant, because they assign a position rather than aggregate over a set.

The practical advice is to write the frame explicitly whenever you write an ORDER BY inside a window and the result has to be reproducible. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW says exactly what you mean and gives the same answer whether or not there are ties. The default RANGE frame will silently give a different answer the day two rows share an ORDER BY value — and that is the kind of bug that appears months later, on a data set that was not supposed to have ties at all.

The frames worth knowing are UNBOUNDED PRECEDING for the start, CURRENT ROW for the end, and BETWEEN n PRECEDING AND n FOLLOWING for a moving window around the current row. That last one is how you write a moving average over a fixed number of neighbours.