Postgres by Example: Window Functions

Window functions compute a value for each row using a window — a set of related rows defined by an OVER (...) clause. Unlike GROUP BY, which collapses rows, window functions add a per-row result alongside the original columns. They are the right tool for rankings, running totals, moving averages, percentile bands, "previous row" comparisons — anything you used to do in your application code by sorting and iterating.

What you'll learn:

• The shape of a window function: f() OVER (PARTITION BY ... ORDER BY ...)
• The most useful functions: row_number, rank, dense_rank, lag, lead, sum, avg, first_value, last_value
• Partitioning vs ordering inside OVER
• Reusable windows with WINDOW name AS (...)
• The frame clause: ROWS BETWEEN ... AND ...

-- Numbering rows within each partition
SELECT customer_id, id AS order_id, total,
       row_number() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn,
       rank()       OVER (PARTITION BY customer_id ORDER BY total DESC) AS rnk
FROM orders_example
ORDER BY customer_id, rn;

-- "Most expensive order per customer" — keep rn = 1
WITH ranked AS (
  SELECT *,
         row_number() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn
  FROM orders_example
)
SELECT customer_id, id, total FROM ranked WHERE rn = 1;

-- lag / lead: look at previous / next row in the partition
SELECT customer_id, id, total,
       lag(total)  OVER (PARTITION BY customer_id ORDER BY id) AS prev_total,
       lead(total) OVER (PARTITION BY customer_id ORDER BY id) AS next_total
FROM orders_example
ORDER BY customer_id, id;

-- Running total (a sum is also a window function)
SELECT customer_id, id, total,
       sum(total) OVER (PARTITION BY customer_id ORDER BY id) AS running_total
FROM orders_example
ORDER BY customer_id, id;

-- Reusable window
SELECT id, total,
       sum(total) OVER w AS running,
       avg(total) OVER w AS running_avg
FROM orders_example
WINDOW w AS (PARTITION BY customer_id ORDER BY id)
ORDER BY customer_id, id;

-- Frame clause: 3-row trailing average
SELECT id, total,
       avg(total) OVER (
         PARTITION BY customer_id
         ORDER BY id
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS trailing_avg
FROM orders_example
ORDER BY customer_id, id;

The window is defined by three pieces:

1. PARTITION BY — splits the rows into independent groups. Like GROUP BY, but each group keeps its rows.
2. ORDER BY — orders rows inside each partition. Determines row_number, lag, running totals, etc.
3. Frame clause (optional) — ROWS BETWEEN ... AND ... or RANGE BETWEEN .... Defines which rows around the current row are in the window. Defaults differ by function family: ranking functions ignore the frame; running aggregates default to "all rows up to and including the current row."

The differences between row_number, rank, and dense_rank matter when there are ties. row_number always assigns 1, 2, 3, ... with ties broken arbitrarily. rank assigns 1, 2, 2, 4 (gap after a tie). dense_rank assigns 1, 2, 2, 3 (no gap).

The "top-N per group" pattern (PARTITION BY group_col ORDER BY rank_col DESC + filter rn = 1) is the canonical use of row_number. It replaces convoluted self-joins and subqueries.

To run (requires orders_example):

$ psql -f source/window-functions.sql postgres
 customer_id | order_id | total | rn | rnk
-------------+----------+-------+----+-----
           1 |        1 | 50.00 |  1 |   1
           1 |        2 | 30.00 |  2 |   2
          99 |        3 | 10.00 |  1 |   1
(3 rows)
 ...

Common pitfalls:

• Window functions are not allowed in WHERE or HAVING (they are computed after those clauses). Wrap the query in a CTE or subquery and filter outside.
• The default frame for sum() OVER (ORDER BY ...) is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — peer rows with equal ORDER BY values are summed into the same total. Use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to be strict about row-by-row running.
• Forgetting ORDER BY inside OVER for lag / lead makes no sense — the function needs an order. PostgreSQL warns.
• Performance: window functions sort the partition. With a matching index on (partition_col, order_col), the sort can be skipped.

Tip: "Show the change vs. previous row" is total - lag(total) OVER (ORDER BY ...). "Show as percent of total" is 100.0 * total / sum(total) OVER (). Both replace a self-join or subquery with a single readable expression.

Try it: Add ntile(4) to put each order into quartiles: ntile(4) OVER (ORDER BY total). Then compute "today's total vs. yesterday's" on a time-series table by lag(daily_total) OVER (ORDER BY day). Then try first_value(total) OVER (PARTITION BY customer_id ORDER BY id) to see the customer's first order amount on every row.

Source: window-functions.sql

Next: String and Numeric Functions

Home: Postgres by Example