How to Use Window Functions in PostgreSQL: Syntax and Examples

In PostgreSQL, use window functions to perform calculations across sets of rows related to the current row without collapsing the result. You write them with an OVER() clause that defines the window or partition of rows to operate on.

Syntax

A window function in PostgreSQL has this basic form:

• function_name(): The aggregate or ranking function like ROW_NUMBER(), SUM(), or AVG().
• OVER(): Defines the window or group of rows the function works on.
• Inside OVER(), you can specify:
• PARTITION BY to divide rows into groups.
• ORDER BY to order rows within each partition.
• ROWS BETWEEN to define a frame of rows relative to the current row.

sql

function_name() OVER ([PARTITION BY partition_expression] [ORDER BY order_expression] [ROWS frame_specification])

Example

This example shows how to assign a row number to each employee within their department, ordered by salary:

sql

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  department TEXT,
  salary INT
);

INSERT INTO employees (name, department, salary) VALUES
('Alice', 'Sales', 5000),
('Bob', 'Sales', 6000),
('Carol', 'HR', 4500),
('Dave', 'HR', 4700),
('Eve', 'Sales', 5500);

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

Output

 name  | department | salary | rank_in_dept 
-------+------------+--------+--------------
 Dave  | HR         |   4700 |            1
 Carol | HR         |   4500 |            2
 Bob   | Sales      |   6000 |            1
 Eve   | Sales      |   5500 |            2
 Alice | Sales      |   5000 |            3

Common Pitfalls

Common mistakes when using window functions include:

• Forgetting the OVER() clause, which causes syntax errors.
• Using aggregate functions without OVER(), which collapses rows instead of keeping them.
• Misunderstanding PARTITION BY and ORDER BY inside OVER(), leading to unexpected results.
• Confusing window functions with GROUP BY aggregates, which change the number of rows returned.

sql

/* Wrong: aggregate without OVER collapses rows */
SELECT department, SUM(salary) FROM employees;

/* Right: window function keeps rows and shows running total */
SELECT name, department, salary, SUM(salary) OVER (PARTITION BY department ORDER BY salary) AS running_total FROM employees;

Quick Reference

Clause	Description	Example
function_name()	The window function like ROW_NUMBER(), RANK(), SUM(), AVG()	ROW_NUMBER()
OVER()	Defines the window for the function	OVER (PARTITION BY department ORDER BY salary)
PARTITION BY	Groups rows into partitions	PARTITION BY department
ORDER BY	Orders rows within each partition	ORDER BY salary DESC
ROWS BETWEEN	Defines frame of rows relative to current row	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Key Takeaways

Use window functions with OVER() to calculate values across related rows without grouping.

PARTITION BY divides rows into groups; ORDER BY sorts rows within each group.

Window functions keep all rows, unlike aggregate functions with GROUP BY.

Common window functions include ROW_NUMBER(), RANK(), SUM(), AVG(), and LAG().

Always include OVER() clause to avoid syntax errors and unexpected results.