PostgreSQL Window Functions

Window functions compute a value across a set of related rows, without collapsing them into a single output row the way GROUP BY does — you keep every original row, plus an extra computed column.

Basic Syntax

function_name(...) OVER (
  PARTITION BY column
  ORDER BY column
)

ROW_NUMBER() — Sequential Numbering

SELECT name, department, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

PARTITION BY — Reset the Window Per Group

Numbers restart within each department, instead of across the whole table.

SELECT name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

RANK() vs DENSE_RANK()

Both assign a rank based on ORDER BY, but handle ties differently:

• RANK() — ties share a rank, then skips the next number(s) (1, 1, 3, 4...).
• DENSE_RANK() — ties share a rank, with no gap afterward (1, 1, 2, 3...).

SELECT name, salary,
  RANK() OVER (ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;

LAG() and LEAD() — Look at Neighboring Rows

Compare each row to the one before (LAG) or after (LEAD) it, in the given order — great for month-over-month comparisons.

SELECT month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS previous_month,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_sales;

Running Totals with SUM() OVER

SELECT month, revenue,
  SUM(revenue) OVER (ORDER BY month) AS running_total
FROM monthly_sales;

Common Errors & Troubleshooting

• ERROR: window functions are not allowed in WHERE — a window function's result can't be filtered directly in the same query's WHERE clause; wrap the query in a subquery/CTE and filter on the outer level instead.
• Forgetting PARTITION BY and getting one continuous ranking instead of per-group rankings — without PARTITION BY, ROW_NUMBER()/RANK() number across the entire result set, not restarting per department/category.
• Confusing RANK() and ROW_NUMBER() for ties — ROW_NUMBER() always gives unique, sequential numbers even for tied values; RANK() gives tied rows the same number. Pick based on whether ties should be distinguishable.

Key Takeaways

• Window functions add a computed column per row, without collapsing rows the way GROUP BY does.
• PARTITION BY resets the calculation for each group; ORDER BY inside OVER() decides the row sequence used.
• LAG/LEAD are the standard tools for "compare this row to the previous/next one" logic like month-over-month growth.