Postgres Window Functions: Running Totals, Rankings and Top-N per Group

GROUP BY collapses rows: one row per customer, with a total. Window functions calculate across related rows but keep every row — so you can show each order and the customer's total, or each order's rank among that customer's orders. (SQL GROUP BY)

The shape

function(...) OVER (
  PARTITION BY ...   -- which rows belong together (like GROUP BY, without collapsing)
  ORDER BY ...       -- order within each partition
  frame ...          -- which rows around the current one to include (optional)
)

The examples use an orders table with id, customer_id, total and created_at.

Each row with its group's total

SELECT id, customer_id, total,
       SUM(total) OVER (PARTITION BY customer_id) AS customer_total,
       round(100.0 * total / SUM(total) OVER (PARTITION BY customer_id), 1) AS pct_of_customer
FROM orders;

Every order row stays, with the customer's total alongside.

Ranking

SELECT customer_id, 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,
       DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY total DESC) AS dense
FROM orders;

Function	Ties
ROW_NUMBER()	Always 1, 2, 3… (ties broken arbitrarily — add a tie-breaker column)
RANK()	Ties share a rank, then skip: 1, 1, 3
DENSE_RANK()	Ties share a rank, no gaps: 1, 1, 2

Top-N per group (the classic)

"Each customer's 3 most recent orders":

SELECT *
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC, id DESC) AS rn
  FROM orders o
) ranked
WHERE rn <= 3;

You can't filter on a window function in WHERE directly (it's computed after WHERE), hence the subquery — or a CTE. (Postgres CTEs)

For "latest one per group", Postgres also has DISTINCT ON:

SELECT DISTINCT ON (customer_id) *
FROM orders
ORDER BY customer_id, created_at DESC;

Running totals

SELECT date_trunc('day', created_at) AS day,
       SUM(total) AS revenue,
       SUM(SUM(total)) OVER (ORDER BY date_trunc('day', created_at)) AS cumulative
FROM orders
GROUP BY day
ORDER BY day;

The window runs over the grouped result. ORDER BY inside OVER makes the sum cumulative.

Moving averages: frames

SELECT day, revenue,
       AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d
FROM daily_revenue;

The frame (ROWS BETWEEN …) picks which neighbouring rows to include — here the current day and the six before it. Watch out: with ORDER BY and no explicit frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which treats rows with equal ordering values as peers — a source of surprises.

Comparing with the previous row: LAG and LEAD

SELECT day, revenue,
       LAG(revenue) OVER (ORDER BY day) AS previous_day,
       revenue - LAG(revenue) OVER (ORDER BY day) AS change
FROM daily_revenue;

LAG looks back, LEAD looks forward. Great for day-over-day changes, time between a user's sessions, or detecting gaps.

Also useful: FIRST_VALUE, LAST_VALUE, NTH_VALUE, NTILE(4) (quartiles), PERCENT_RANK().

Naming windows

Reuse a window definition:

SELECT id,
       SUM(total) OVER w AS running,
       AVG(total) OVER w AS running_avg
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY created_at);

Performance

Window functions sort data by PARTITION BY + ORDER BY. An index on those columns (e.g. (customer_id, created_at)) can let Postgres avoid a big sort. Check with EXPLAIN ANALYZE. (Reading EXPLAIN ANALYZE, Database indexes)

---

EasySpawn gives your app its own Postgres database and a terminal with Claude Code, so "revenue by day with a 7-day average" is one question away. See how it works or join the waitlist.

Related: Postgres CTEs · SQL GROUP BY Explained · WHERE vs HAVING · Postgres Materialized Views