PostgreSQL Window Functions: Queries, Frames, and Examples

Formulas & functions · Updated November 22, 2026

PostgreSQL window functions calculate a result across related rows while keeping each row in the query result. Use an OVER clause to define which rows are related and, optionally, how they are ordered; this makes window functions useful for rankings, running totals, comparisons with previous rows, and moving calculations.

What a window function does

A regular aggregate such as SUM(amount) normally combines many input rows into a smaller number of output rows, often one per group. A window function works across a set of rows associated with the current row, but returns a value for each row. That difference lets a query show detail and summary information together.

For example, a grouped query could return one sales total per salesperson. A window query can return every sale and also show that salesperson’s total beside each sale. The rows remain individually available for filtering, sorting, or further calculations in an outer query.

PostgreSQL supports dedicated window functions, including ranking and navigation functions, and also allows many aggregate functions to be used as window functions. The essential syntax is function_name(...) OVER (...). The parentheses after OVER describe the window; they do not group or remove output rows.

The OVER clause: partitioning and ordering

A window definition can include a partition, an ordering, and a frame. Each part answers a different question:

  • PARTITION BY divides the input into independent groups. A calculation restarts for each partition.
  • ORDER BY establishes the sequence of rows within each partition. It matters for rankings, previous-row comparisons, and running calculations.
  • A frame selects which rows in the partition are considered for the current row. It is especially important for aggregate window calculations and value functions.

Suppose a table named orders contains customer_id, order_date, and amount. This query shows each order and a cumulative amount for that customer:

SELECT customer_id,
       order_date,
       amount,
       SUM(amount) OVER (
           PARTITION BY customer_id
           ORDER BY order_date, order_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_amount
FROM orders;

The partition restarts the sum for each customer. The ordering puts a customer’s orders into sequence; adding a unique tie-breaker such as order_id makes the sequence predictable when dates match. The frame includes rows from the beginning of that customer’s sequence through the current order.

Ranking and distribution functions

PostgreSQL’s ranking functions answer related but distinct questions. They are often used to rank products within categories, find top employees per department, or divide a result into buckets.

  • ROW_NUMBER() assigns a distinct sequential number to each row. If the ordering values tie, the tied rows still receive different numbers, but their relative order is unspecified unless the sort includes a tie-breaker.
  • RANK() gives tied rows the same rank and leaves gaps after ties. If two rows share rank 1, the next rank is 3.
  • DENSE_RANK() also gives ties the same rank, but does not leave gaps. The next rank in that example is 2.
  • PERCENT_RANK() expresses a row’s relative rank as a fraction from 0 to 1. A single-row partition has a value of 0.
  • CUME_DIST() gives the fraction of rows in the partition that precede or are peers with the current row.
  • NTILE(n) divides rows into up to n numbered groups, distributing rows as evenly as possible.

For example, to rank products by revenue within each category, use RANK() OVER (PARTITION BY category_id ORDER BY revenue DESC). Rows with equal revenue receive the same rank. Add product_id to the ordering if you also need a stable order among ties, but be aware that adding a unique value to RANK’s ordering means those rows no longer count as peers and therefore will not share a rank.

Aggregate windows for totals and averages

Aggregates such as SUM, AVG, COUNT, MIN, and MAX can be followed by OVER to calculate over a window rather than collapsing rows. Without a window ordering, an aggregate window can show a whole-partition value on every row. With an ordering and an appropriate frame, it can produce a running or moving calculation.

SELECT department_id,
       employee_id,
       salary,
       AVG(salary) OVER (PARTITION BY department_id) AS department_average,
       salary - AVG(salary) OVER (PARTITION BY department_id) AS difference_from_average
FROM employees;

Every employee stays in the output, with the department average repeated for that department. To calculate a moving average over the current and two preceding rows, specify a frame such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. The first rows in a partition have fewer than three rows available, so their averages use the rows that exist in the frame.

Aggregate windows can also use a FILTER clause to include only rows meeting a condition, for example COUNT(*) FILTER (WHERE status = 'paid') OVER (PARTITION BY customer_id). This counts paid rows within each customer’s partition while retaining all rows in the query result.

Frames, defaults, and the LAST_VALUE surprise

Frames are a frequent source of unexpected results. If a window has ORDER BY but no explicit frame, PostgreSQL’s default frame runs from the start of the partition through the current row and its ordering peers. Because peers share the same ordering values, this default is not always equivalent to a row-by-row running total.

For a strictly sequential running calculation, use a ROWS frame and make the ordering deterministic, typically by including a unique key. A ROWS frame counts physical rows in the ordered partition. Other frame modes, such as RANGE and GROUPS, have different peer and ordering-value behavior; choose one deliberately when ties or value-based ranges matter.

The default frame also explains a common surprise with LAST_VALUE. It returns the value from the last row of the current frame, not necessarily the last row of the entire partition. To get the partition’s final value for every row, explicitly extend the frame:

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

By contrast, FIRST_VALUE returns the first value in the frame, and NTH_VALUE returns the requested position when that position is present in the frame. These functions are frame-sensitive too. When their result seems limited to the current row or an early portion of the partition, inspect the frame before changing the query.

LAG and LEAD for comparing rows

LAG(value) reads a value from an earlier row in the ordered partition; LEAD(value) reads one from a later row. They are useful for period-over-period changes, event sequences, and identifying transitions without joining a table to itself.

SELECT account_id,
       day,
       balance,
       balance - LAG(balance) OVER (
           PARTITION BY account_id
           ORDER BY day
       ) AS change_from_previous_day
FROM daily_balances;

The first row of each account has no preceding row, so LAG returns NULL there unless a default value is supplied as its third argument. For example, LAG(balance, 1, 0) uses zero when that preceding row does not exist. This default does not replace a stored NULL value in an existing preceding row. As with rankings, include enough ordering columns to define the intended sequence when dates can repeat.

Filtering window results and query order

A window function cannot be used directly in the same query level’s WHERE, GROUP BY, or HAVING clause. Those clauses are processed before window calculations at that query level. Window functions are commonly placed in the SELECT list or the query’s ORDER BY; to filter on a window result, calculate it in a subquery or a common table expression, then filter outside.

WITH ranked_orders AS (
    SELECT customer_id,
           order_id,
           amount,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY amount DESC, order_id
           ) AS position
    FROM orders
)
SELECT customer_id, order_id, amount
FROM ranked_orders
WHERE position <= 3;

This returns up to three orders per customer. The CTE is a logical query structure, not a guarantee that PostgreSQL will materialize an intermediate result; the planner can choose an execution strategy. The same pattern works with a derived table in the FROM clause.

Named windows, performance, and practical choices

If several expressions share the same partitioning and ordering, a named window can make the query easier to read. Define it with a WINDOW clause after WHERE and before the query-level ORDER BY, then refer to its name in each OVER clause. Named windows reduce repetition but do not change the calculation’s meaning.

Window calculations can require sorting, particularly when the input is not already arranged in a useful order. Large partitions and multiple incompatible window orderings may increase memory use or lead to disk-backed sorting. An index whose leading columns match a partition and ordering can sometimes help, but it does not guarantee that PostgreSQL will avoid a sort or choose that index. Check the actual plan with EXPLAIN or EXPLAIN (ANALYZE, BUFFERS) on representative data; ANALYZE executes the query.

For readable and dependable SQL, decide what defines a partition, what constitutes a tie, and what the frame should contain before choosing a function. In particular:

  • Use ROW_NUMBER when you need a fixed number of rows per group; specify tie-breakers if the selected rows must be repeatable.
  • Use RANK or DENSE_RANK when equal values should share a rank.
  • Use an explicit ROWS frame for a row-by-row cumulative or moving calculation.
  • Use a subquery or CTE when filtering, grouping, or joining based on a window result.

Window functions complement, rather than replace, GROUP BY. Grouping is the right tool when the desired output has one row per group; a window is the right tool when detail rows should remain visible alongside calculations over related rows.

Frequently Asked Questions

Do PostgreSQL window functions reduce the number of rows?
No. They calculate values for rows in the query result without combining those rows into one per group. A separate filter or grouping operation can change the row count.

Can I use a window function in WHERE?
Not at the same query level. Compute the window value in a subquery or CTE, then apply the condition in an outer query.

What is the difference between RANK and DENSE_RANK?
Both give the same rank to tied values. RANK leaves gaps after ties, while DENSE_RANK assigns the next consecutive rank.

Why does LAST_VALUE return an unexpected value?
It reads from the end of the current frame, which may end at the current row or its peers. To return the partition’s final value, set a frame ending with UNBOUNDED FOLLOWING.

Are window functions available in PostgreSQL?
Yes. PostgreSQL supports the SQL OVER syntax, ranking functions, row-navigation and value functions, and aggregate functions used as windows. Consult documentation for the PostgreSQL version in use when relying on version-specific features.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.