Window Functions

An aggregate such as sum() squeezes many rows into one. A window function calculates across a set of related rows but keeps every row in the result. That is how you add a rank, a running total or "change since last month" next to each row, without losing the detail.

The SQL course introduces the idea. In this lesson you will use the full PostgreSQL toolkit: ranking functions, lag and lead, window frames for moving averages, named windows, and the top-N-per-group pattern.

The OVER clause

Any function followed by OVER (...) is a window function. Inside the brackets:

• PARTITION BY splits the rows into groups (like GROUP BY, but rows are not merged).
• ORDER BY sets the order of rows inside each partition.
• An optional frame says which rows around the current row are included.

Ranking staff by salary inside each department shows the three ranking functions side by side:

SELECT department, first_name, salary,
       row_number() OVER w AS row_num,
       rank()       OVER w AS rnk,
       dense_rank() OVER w AS dense_rnk
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC)
ORDER BY department, salary DESC;

department  | first_name | salary | row_num | rnk | dense_rnk
------------+------------+--------+---------+-----+----------
Engineering | Neha       | 180000 | 1       | 1   | 1
Engineering | Amit       | 120000 | 2       | 2   | 2
Engineering | Pooja      | 75000  | 3       | 3   | 3
Engineering | Karan      | 70000  | 4       | 4   | 4
Management  | Rajesh     | 250000 | 1       | 1   | 1
Sales       | Simran     | 110000 | 1       | 1   | 1
Sales       | Rahul      | 45000  | 2       | 2   | 2
Sales       | Nisha      | 42000  | 3       | 3   | 3
Support     | Deepak     | 65000  | 1       | 1   | 1
Support     | Tanvi      | 38000  | 2       | 2   | 2
(10 rows)

row_number always counts 1, 2, 3. rank gives equal values the same rank and then skips numbers, and dense_rank gives equal values the same rank without gaps. The two Sales Executives earn different amounts here, so all three agree; with a tie, you would see the difference. The WINDOW w AS (...) clause names a window definition once so several functions can share it, which keeps long queries readable.

Running totals and share of total

With ORDER BY inside OVER, sum() becomes a running total. Without ORDER BY, it is the total of the whole partition, which is perfect for percentages:

WITH daily AS (
  SELECT o.order_date, sum(oi.quantity * oi.unit_price) AS sales
  FROM orders AS o
  JOIN order_items AS oi ON oi.order_id = o.order_id
  WHERE o.status <> 'cancelled'
  GROUP BY o.order_date
)
SELECT order_date,
       sales,
       sum(sales) OVER (ORDER BY order_date) AS running_total,
       round(100 * sales / sum(sales) OVER (), 1) AS pct_of_all
FROM daily
ORDER BY order_date
LIMIT 6;

order_date | sales    | running_total | pct_of_all
-----------+----------+---------------+-----------
2025-01-05 | 2147.00  | 2147.00       | 2.1
2025-01-12 | 15999.00 | 18146.00      | 15.3
2025-01-20 | 5398.00  | 23544.00      | 5.2
2025-02-02 | 8999.00  | 32543.00      | 8.6
2025-02-28 | 3948.00  | 36491.00      | 3.8
2025-03-09 | 2499.00  | 38990.00      | 2.4
(6 rows)

OVER () with empty brackets means "all rows", so every row is divided by the grand total. The query first builds daily sales in a CTE (a named subquery, covered in the next lesson) and then adds the window columns.

Looking at the previous and next row

lag(value, n) reads a value from n rows earlier in the partition (default 1), and lead reads from later rows. They answer questions like "how many days since this customer's previous order?":

SELECT customer_id, order_id, order_date,
       lag(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_order,
       order_date - lag(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS days_gap
FROM orders
WHERE customer_id IN (1, 2, 3)
ORDER BY customer_id, order_date;

customer_id | order_id | order_date | previous_order | days_gap
------------+----------+------------+----------------+---------
1           | 101      | 2025-01-05 | NULL           | NULL
1           | 104      | 2025-02-02 | 2025-01-05     | 28
1           | 115      | 2025-05-20 | 2025-02-02     | 107
2           | 102      | 2025-01-12 | NULL           | NULL
2           | 109      | 2025-03-22 | 2025-01-12     | 69
3           | 103      | 2025-01-20 | NULL           | NULL
3           | 113      | 2025-05-02 | 2025-01-20     | 102
(7 rows)

The first order of each customer has no previous row, so lag returns NULL and the gap is NULL too. first_value, last_value and nth_value work in a similar way, reading a value from a fixed position in the window.

Window frames: moving averages

The frame decides which rows the function sees for each row. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means "this row and the two before it", which gives a three-order moving average:

SELECT order_id,
       order_total,
       round(avg(order_total) OVER (ORDER BY order_id
             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3
FROM (
  SELECT order_id, sum(quantity * unit_price) AS order_total
  FROM order_items
  GROUP BY order_id
) AS t
ORDER BY order_id
LIMIT 5;

order_id | order_total | moving_avg_3
---------+-------------+-------------
101      | 2147.00     | 2147.00
102      | 15999.00    | 9073.00
103      | 5398.00     | 7848.00
104      | 8999.00     | 10132.00
105      | 6499.00     | 6965.33
(5 rows)

For order 101 the frame holds only one row, and for 102 two rows, so the first averages use fewer orders. There are three frame modes:

Top N per group

Window functions cannot go in WHERE, because WHERE runs before they are calculated. Wrap the query and filter outside. This finds the two best-paid people in each department:

SELECT department, first_name, salary
FROM (
  SELECT department, first_name, salary,
         dense_rank() OVER (PARTITION BY department ORDER BY salary DESC) AS pos
  FROM employees
) AS ranked
WHERE pos <= 2
ORDER BY department, pos;

department  | first_name | salary
------------+------------+-------
Engineering | Neha       | 180000
Engineering | Amit       | 120000
Management  | Rajesh     | 250000
Sales       | Simran     | 110000
Sales       | Rahul      | 45000
Support     | Deepak     | 65000
Support     | Tanvi      | 38000
(7 rows)

Using dense_rank means two people tied for second place both appear. Choose row_number if you need exactly two rows per group.

Common mistakes

• Putting a window function in WHERE. Filter in an outer query or CTE.
• Forgetting PARTITION BY. Without it, ranks and running totals run across the whole table instead of restarting per group.
• Using last_value with the default frame. Add an explicit frame that reaches the end of the partition.
• Dividing integers for percentages. 100 * a / b on integers drops the fraction; use numeric values.

Key points

• Window functions add a calculated value to each row without merging rows.
• PARTITION BY groups rows, ORDER BY orders them, and the frame picks the rows each calculation sees.
• row_number, rank and dense_rank rank rows; lag and lead read neighbouring rows.
• sum() OVER (ORDER BY ...) gives a running total; sum() OVER () gives the grand total.
• WINDOW w AS (...) names a window definition for reuse.
• To filter on a window result, compute it in a subquery or CTE first.

SELECT category, product_name, price,
       round(avg(price) OVER (PARTITION BY category), 2) AS category_avg,
       round(price - avg(price) OVER (PARTITION BY category), 2) AS diff_from_avg
FROM products
ORDER BY category, price;