Tutorials › PostgreSQL Essentials › Window Functions

Window Functions

7 min read Quiz at the end

Window functions calculate across related rows without collapsing them into one. ROW_NUMBER(), RANK(), LAG(), LEAD(), and running SUM() OVER() are common. They are essential for rankings and period comparisons.

Window Functions

-- Rank users by total spending
SELECT name, total,
  RANK() OVER (ORDER BY total DESC) AS rank
FROM orders;

-- Running total
SELECT date, total,
  SUM(total) OVER (ORDER BY date) AS running_total
FROM orders;

-- Row number per partition
SELECT city, name,
  ROW_NUMBER() OVER (PARTITION BY city ORDER BY name) AS rn
FROM users;

← CTEs (WITH Clause) Next: Indexes →

Topic Quiz · 5 questions

Test your understanding before moving on

1. Which function assigns a rank with no gaps?

💡 DENSE_RANK() assigns ranks without gaps (1,2,2,3 not 1,2,2,4).

2. What does OVER() define?

💡 OVER() defines the window (partition and ordering) for a window function.

3. Which function computes a running total?

💡 SUM() with OVER(ORDER BY date) computes a cumulative running total.

4. What does PARTITION BY do?

💡 PARTITION BY divides rows into groups; the function resets for each group.

5. Which window function assigns unique sequential integers?

💡 ROW_NUMBER() assigns unique sequential integers starting at 1 per partition.

Quick Access

Window Functions

Window Functions

Test your understanding before moving on