Window Functions in PostgreSQL

There's a class of question that GROUP BY can't answer without contortions. Rank each customer's orders from newest to oldest. Show each order next to that customer's running total. Compare this month's revenue to last month's. All of these need a calculation that spans multiple rows while still returning every individual row. That's exactly what window functions do, and once they click, you'll reach for them constantly.

This is part 7 of the PostgreSQL Masterclass, following aggregations. Window functions are the single biggest step up in SQL power for most developers, so this one is worth slowing down for.

The core idea: aggregate without collapsing

A GROUP BY takes many rows and returns one per group. A window function computes across a set of rows, called the window, but returns a value for every row. The rows stay; you just get a calculation attached to each one.

The syntax is an aggregate or ranking function followed by OVER (...):

SELECT
  customer_id,
  total,
  sum(total) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

Every order row comes back, and each one carries its customer's total alongside it. No collapsing, no subquery, no self-join. The PARTITION BY splits the rows into groups the same way GROUP BY would, but the output keeps every row.

PARTITION BY and ORDER BY inside OVER

The window has two knobs. PARTITION BY decides which rows share a window. ORDER BY inside OVER decides the order within it, which matters for anything sequential like ranking or running totals.

SELECT
  customer_id,
  created_at,
  total,
  row_number() OVER (PARTITION BY customer_id ORDER BY created_at) AS order_seq,
  sum(total)   OVER (PARTITION BY customer_id ORDER BY created_at) AS running_total
FROM orders;

order_seq numbers each customer's orders 1, 2, 3 in date order. running_total accumulates as it goes, because adding ORDER BY to a sum window turns it into a running sum. That's the detail people miss: a windowed aggregate with ORDER BY computes cumulatively from the first row up to the current one, not over the whole partition.

Ranking functions

Three ranking functions look similar and differ in how they handle ties:

SELECT
  product,
  revenue,
  row_number() OVER (ORDER BY revenue DESC) AS rn,
  rank()       OVER (ORDER BY revenue DESC) AS rnk,
  dense_rank() OVER (ORDER BY revenue DESC) AS dense
FROM product_sales;

• row_number() gives a unique number to every row, breaking ties arbitrarily. Use it to pick exactly one row per group.
• rank() gives tied rows the same rank, then skips the next values (1, 2, 2, 4).
• dense_rank() gives tied rows the same rank with no gaps (1, 2, 2, 3).

The most common real use of row_number() is "the latest row per group," which is awkward with GROUP BY and clean with a window:

-- most recent order per customer
SELECT * FROM (
  SELECT *,
    row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders
) t
WHERE rn = 1;

LAG and LEAD: reaching across rows

lag() and lead() pull a value from a previous or following row in the window. This is how you compute change over time without a self-join:

SELECT
  month,
  revenue,
  lag(revenue) OVER (ORDER BY month) AS prev_month,
  revenue - lag(revenue) OVER (ORDER BY month) AS mom_change
FROM monthly_revenue;

lag(revenue) returns the prior row's revenue, so mom_change is this month minus last month. The first row has NULL for lag because there's nothing before it; lag(revenue, 1, 0) supplies a default of 0 if you'd rather not have the null. lead() is the same idea looking forward.

Frames: controlling exactly which rows count

By default, a windowed aggregate with ORDER BY uses a frame that runs from the start of the partition to the current row, which is what makes running totals work. You can set the frame explicitly to compute moving averages and other sliding calculations:

-- 7-day moving average of daily revenue
SELECT
  day,
  revenue,
  avg(revenue) OVER (
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_revenue;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW averages the current day and the 6 before it. Frames are the part of window functions people rarely learn, and they unlock moving averages, trailing sums, and "compare to the previous N rows" in a single expression.

A note on performance

Window functions run after WHERE and GROUP BY, over the rows that survive filtering. They don't magically avoid work, so on large tables the sort implied by ORDER BY inside OVER can be the expensive part. An index matching the PARTITION BY and ORDER BY columns lets Postgres skip that sort. As always, we'll confirm with EXPLAIN rather than guessing, later in the series.

Window functions replace a lot of code. Running totals, rankings, "latest per group," period-over-period comparisons, and moving averages were each a subquery or an application loop before, and they're each one line now. If you write reporting or analytics queries, this is the highest-value SQL you can learn.

Next, we finish the SQL module with common table expressions and recursive queries, which make complex queries readable and let you walk tree-shaped data.