Postgres Window Functions Cheatsheet

Overview

Window functions compute a value for each row using a set of rows related to it (the “window”), without collapsing rows like GROUP BY does. They run after WHERE, GROUP BY, and HAVING but before ORDER BY and LIMIT. Use them for rankings, running totals, moving averages, and comparing each row to its neighbors. For query plan interpretation see postgres-explain.

The general syntax: function_name([args]) OVER ([PARTITION BY ...] [ORDER BY ...] [frame_clause]).

Ranking functions

Ranking functions assign a position to each row within a partition.

Function	Behavior on ties	Gaps after ties	Example output for scores 90,90,80
ROW_NUMBER()	Arbitrary order within ties	No gaps	1, 2, 3
RANK()	Same rank for ties	Gap after tie group	1, 1, 3
DENSE_RANK()	Same rank for ties	No gaps	1, 1, 2
NTILE(n)	Distribute rows into n buckets	N/A	bucket number 1..n
PERCENT_RANK()	(rank-1) / (total_rows-1)	N/A	0.0 to 1.0
CUME_DIST()	rows up to and including / total	N/A	0 < value ⇐ 1.0

SELECT
  name,
  score,
  RANK()        OVER (ORDER BY score DESC) AS rank,
  DENSE_RANK()  OVER (ORDER BY score DESC) AS dense_rank,
  ROW_NUMBER()  OVER (ORDER BY score DESC) AS row_num
FROM results;

Use DENSE_RANK when you want to display “2nd place” without skipping numbers after ties. Use ROW_NUMBER when you need a unique identifier for pagination.

Offset functions: LAG and LEAD

LAG and LEAD access a row at a given offset before or after the current row within the window.

Function	Returns	Optional args
LAG(col, offset, default)	Value from offset rows before	offset defaults to 1; default if no row exists
LEAD(col, offset, default)	Value from offset rows after	Same defaults

SELECT
  date,
  revenue,
  LAG(revenue, 1, 0)  OVER (ORDER BY date) AS prev_revenue,
  LEAD(revenue, 1, 0) OVER (ORDER BY date) AS next_revenue,
  revenue - LAG(revenue, 1, 0) OVER (ORDER BY date) AS delta
FROM daily_revenue;

The default argument prevents NULL from appearing when there is no prior or next row. Without it, the first row’s LAG is NULL.

Aggregate window functions

Any aggregate function works as a window function by adding an OVER clause. Rows are not collapsed.

Pattern	Example	Returns
Running sum	SUM(amount) OVER (ORDER BY date)	Cumulative total at each row
Running average	AVG(score) OVER (ORDER BY date)	Cumulative average
Partition total	SUM(amount) OVER (PARTITION BY dept)	Dept total on every row in that dept
Count per partition	COUNT(*) OVER (PARTITION BY category)	Group size on every row
Max in partition	MAX(score) OVER (PARTITION BY team)	Team max on every row

SELECT
  dept,
  employee,
  salary,
  SUM(salary)  OVER (PARTITION BY dept) AS dept_total,
  ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY dept), 1) AS pct_of_dept
FROM employees;

PARTITION BY and ORDER BY in OVER

PARTITION BY divides rows into independent groups; ORDER BY determines row sequence within each partition.

Clause	Effect when omitted
No PARTITION BY	Entire result set is one partition
No ORDER BY	Aggregate covers all rows in partition; ranking is undefined
Both PARTITION BY and ORDER BY	Standard partitioned running computation

-- Running total per user, reset for each user
SELECT
  user_id,
  event_date,
  SUM(events) OVER (
    PARTITION BY user_id
    ORDER BY event_date
  ) AS running_total
FROM user_events;

Frame clauses

A frame clause narrows the window further within the ordered partition. The default frame depends on whether ORDER BY is present.

When	Default frame
No ORDER BY in OVER	RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (all rows in partition)
ORDER BY present	RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Frame syntax	Covers
ROWS BETWEEN n PRECEDING AND CURRENT ROW	n rows before current (inclusive)
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING	Current row and immediate neighbors
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	From partition start to current
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING	From current to partition end
RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW	Rows within 7 days (date column required)

-- 3-row moving average
SELECT
  date,
  revenue,
  AVG(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3
FROM daily_revenue;

ROWS counts physical rows; RANGE uses value distances on the ORDER BY column and handles ties differently.

Common gotchas

• Window functions cannot appear in WHERE or HAVING. Wrap the query in a CTE or subquery to filter on a window result.
• ORDER BY inside OVER is separate from the query’s ORDER BY. The query can be sorted differently from the window.
• FIRST_VALUE and LAST_VALUE with the default frame (UNBOUNDED PRECEDING TO CURRENT ROW) will give LAST_VALUE of only the current row in the middle of a partition. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the true last value.
• AVG window with ORDER BY produces a running average, not a partition average. Remove ORDER BY to get a partition-wide average on every row.
• Multiple OVER clauses with the same definition can be named with the WINDOW clause to avoid repetition: WINDOW w AS (PARTITION BY dept ORDER BY salary).
• The WindowAgg node in EXPLAIN wraps a Sort. If the sort is large, check whether the same column is indexed and the plan can use an Index Scan to avoid the sort.

Related

• postgres
• postgres-explain
• sql-joins
• postgres-functions
• postgres-types
• prisma
• Cheatsheets