PostgreSQL window functions

A window function computes a value across a set of related rows without collapsing them, unlike GROUP BY. Every row stays, and gains a column like “rank within department” or “running total so far”.

The OVER (...) clause defines the window: PARTITION BY splits rows into groups (like GROUP BY, but rows are kept); ORDER BY orders rows inside each partition; an optional frame (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) narrows it further.

Ranking: row_number() (1,2,3,4), rank() (1,2,2,4), dense_rank() (1,2,2,3), ntile(n), percent_rank().
Offsets: lag(col) (previous row), lead(col) (next row), first_value, last_value.
Any aggregate with OVER: sum(x) OVER (ORDER BY d) is a running total.

Window functions run after WHERE/GROUP BY, so to filter on them (e.g. “top 2 per group”) wrap the query in a CTE or subquery.

Syntax

function() OVER (
  PARTITION BY col
  ORDER BY col
  [ROWS BETWEEN ... AND ...]
)

Examples

Edit the code and run it. Output is printed the way psql prints it.

14.1Ranking inside groups

SELECT dept_id, first_name, salary,
       rank()       OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_in_dept,
       row_number() OVER (ORDER BY salary DESC)                      AS overall_position
FROM employees
WHERE dept_id IS NOT NULL
ORDER BY dept_id, rank_in_dept;

All 14 rows remain. PARTITION BY dept_id restarts the ranking for each department; the second window has no partition, so it ranks across the whole table.

14.2Compare with the group average

SELECT first_name, dept_id, salary,
       round(avg(salary) OVER (PARTITION BY dept_id)) AS dept_avg,
       salary - round(avg(salary) OVER (PARTITION BY dept_id)) AS diff
FROM employees
WHERE dept_id IS NOT NULL
ORDER BY dept_id, salary DESC;

An ordinary aggregate used as a window: each row sees its department’s average next to its own salary, with no self-join or subquery needed.

14.3Running total and month-over-month change

WITH monthly AS (
  SELECT date_trunc('month', o.order_date)::date AS month,
         sum(oi.quantity * oi.unit_price) AS revenue
  FROM orders o JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status <> 'cancelled'
  GROUP BY 1
)
SELECT month, revenue,
       sum(revenue) OVER (ORDER BY month) AS running_total,
       lag(revenue) OVER (ORDER BY month) AS prev_month,
       revenue - lag(revenue) OVER (ORDER BY month) AS change
FROM monthly
ORDER BY month;

sum() OVER (ORDER BY month) adds up every month so far. lag(revenue) fetches the previous month’s value; it is NULL for the first row.

14.4Top N per group

WITH ranked AS (
  SELECT p.category, p.name, p.price,
         row_number() OVER (PARTITION BY p.category ORDER BY p.price DESC) AS rn
  FROM products p
)
SELECT category, name, price
FROM ranked
WHERE rn <= 2
ORDER BY category, rn;

Number products within each category by price, then keep numbers 1 and 2. The filter must be outside because WHERE runs before window functions.

14.5Named windows and moving averages

SELECT id, order_date,
       count(*) OVER w AS orders_so_far,
       round(avg(id) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS moving_avg_id
FROM orders
WINDOW w AS (ORDER BY order_date)
ORDER BY order_date;

WINDOW w AS (...) defines a window once and reuses it. The frame ROWS BETWEEN 2 PRECEDING AND CURRENT ROW averages the current row with the two before it.

Exercises

Write a query and check it. Any query that returns the right rows passes.

Exercise 14.1

Rank employees by salary within each department using dense_rank(). Show first_name, dept_id, salary, salary_rank. Include all employees.

Hint

dense_rank() OVER (PARTITION BY dept_id ORDER BY salary DESC)

Solution

SELECT first_name, dept_id, salary, dense_rank() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS salary_rank FROM employees;

Exercise 14.2

For each customer’s orders show customer_id, id, order_date and order_number (1 for their first order, 2 for the second, …).

Hint

row_number() OVER (PARTITION BY customer_id ORDER BY order_date)

Solution

SELECT customer_id, id, order_date, row_number() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_number FROM orders;

Exercise 14.3

Find the single most expensive product in each category using a window function. Columns: category, name, price.

Hint

row_number in a CTE, then WHERE rn = 1

Solution

WITH r AS (SELECT category, name, price, row_number() OVER (PARTITION BY category ORDER BY price DESC) rn FROM products) SELECT category, name, price FROM r WHERE rn = 1;