Window Functions

A window function computes a value for each row using a set of related rows (its window), without collapsing rows the way GROUP BY does.

Text

function() OVER (PARTITION BY group_column ORDER BY sort_column frame)

PARTITION BY splits rows into groups (optional).
ORDER BY orders rows inside each group.
The frame picks which rows around the current row are used (ROWS BETWEEN ...).

Window functions run after WHERE, GROUP BY, and HAVING, so you cannot filter on them in the same query's WHERE. Wrap the query in a subquery or CTE.

Example 1: aggregate without collapsing rows

Each employee next to their department's average.

SQL

SELECT name, dept_id, salary,
       round(avg(salary) OVER (PARTITION BY dept_id)) AS dept_avg
FROM employees
WHERE dept_id IN (1, 2)
ORDER BY dept_id, id;

Output

  name  | dept_id | salary | dept_avg 
--------+---------+--------+----------
 Meera  |       1 | 250000 |   168000
 Ravi   |       1 | 180000 |   168000
 Kiran  |       1 | 150000 |   168000
 Neha   |       1 | 150000 |   168000
 Arjun  |       1 | 110000 |   168000
 Divya  |       2 | 160000 |   111667
 Sameer |       2 |  90000 |   111667
 Pooja  |       2 |  85000 |   111667
(8 rows)

Example 2: ROW_NUMBER

Numbers rows 1, 2, 3 inside each partition. Ties get different numbers, in an arbitrary order unless ORDER BY breaks them.

SQL

SELECT name, dept_id, salary,
       row_number() OVER (PARTITION BY dept_id ORDER BY salary DESC, id) AS rn
FROM employees
WHERE dept_id IN (1, 2)
ORDER BY dept_id, rn;

Output

  name  | dept_id | salary | rn 
--------+---------+--------+----
 Meera  |       1 | 250000 |  1
 Ravi   |       1 | 180000 |  2
 Kiran  |       1 | 150000 |  3
 Neha   |       1 | 150000 |  4
 Arjun  |       1 | 110000 |  5
 Divya  |       2 | 160000 |  1
 Sameer |       2 |  90000 |  2
 Pooja  |       2 |  85000 |  3
(8 rows)

Example 3: RANK vs DENSE_RANK

Kiran and Neha tie at 150000.

Function	Ties	After a tie
`row_number()`	different numbers	no gaps
`rank()`	same rank	skips numbers (1, 2, 2, 4)
`dense_rank()`	same rank	no gaps (1, 2, 2, 3)

SQL

SELECT name, salary,
       row_number() OVER w AS row_num,
       rank()       OVER w AS rnk,
       dense_rank() OVER w AS dense
FROM employees
WHERE dept_id = 1
WINDOW w AS (ORDER BY salary DESC)
ORDER BY salary DESC, id;

Output

 name  | salary | row_num | rnk | dense 
-------+--------+---------+-----+-------
 Meera | 250000 |       1 |   1 |     1
 Ravi  | 180000 |       2 |   2 |     2
 Kiran | 150000 |       3 |   3 |     3
 Neha  | 150000 |       4 |   3 |     3
 Arjun | 110000 |       5 |   5 |     4
(5 rows)

The WINDOW clause names a window so it can be reused.

Example 4: top N per group

The highest paid two employees in each department. Filter in an outer query.

SQL

SELECT dept_id, name, salary
FROM (
    SELECT dept_id, name, salary,
           dense_rank() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS r
    FROM employees
    WHERE dept_id IS NOT NULL
) t
WHERE r <= 2
ORDER BY dept_id, salary DESC, name;

Output

 dept_id |  name  | salary 
---------+--------+--------
       1 | Meera  | 250000
       1 | Ravi   | 180000
       2 | Divya  | 160000
       2 | Sameer |  90000
       3 | Vikram | 120000
       3 | Anita  |  80000
       4 | Rohan  |  95000
(7 rows)

Example 5: Nth highest salary

The 3rd highest distinct salary. dense_rank handles ties correctly.

SQL

SELECT DISTINCT salary
FROM (SELECT salary, dense_rank() OVER (ORDER BY salary DESC) AS r FROM employees) t
WHERE r = 3;

Output

 salary 
--------
 160000
(1 row)

Example 6: LAG and LEAD

lag(col, n) reads the value n rows before, lead reads n rows after. Missing rows give NULL.

SQL

SELECT id, order_date,
       lag(order_date) OVER (ORDER BY order_date) AS previous_order,
       order_date - lag(order_date) OVER (ORDER BY order_date) AS days_since
FROM orders
WHERE customer_id = 1
ORDER BY order_date;

Output

 id | order_date | previous_order | days_since 
----+------------+----------------+------------
  1 | 2024-06-01 |                |           
  2 | 2024-06-15 | 2024-06-01     |         14
  6 | 2024-07-05 | 2024-06-15     |         20
(3 rows)

Example 7: running total

With ORDER BY and no explicit frame, sum accumulates from the first row to the current one.

SQL

WITH totals AS (
    SELECT o.id, o.order_date, sum(p.price * oi.quantity) AS total
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.id
    JOIN products p ON p.id = oi.product_id
    GROUP BY o.id, o.order_date
)
SELECT id, order_date, total,
       sum(total) OVER (ORDER BY order_date, id) AS running_total
FROM totals
ORDER BY order_date, id;

Output

 id | order_date | total  | running_total 
----+------------+--------+---------------
  1 | 2024-06-01 | 950.00 |        950.00
  3 | 2024-06-03 | 460.00 |       1410.00
  2 | 2024-06-15 |  50.00 |       1460.00
  5 | 2024-06-20 | 900.00 |       2360.00
  4 | 2024-07-01 |  75.00 |       2435.00
  6 | 2024-07-05 |  75.00 |       2510.00
  7 | 2024-07-06 | 200.00 |       2710.00
  8 | 2024-07-10 | 170.00 |       2880.00
  9 | 2024-07-12 | 125.00 |       3005.00
(9 rows)

Example 8: moving average with a frame

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW averages the current row and the two before it.

SQL

SELECT id, salary,
       round(avg(salary) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)) AS moving_avg
FROM employees
WHERE id <= 5
ORDER BY id;

Output

 id | salary | moving_avg 
----+--------+------------
  1 | 250000 |     250000
  2 | 180000 |     215000
  3 | 150000 |     193333
  4 | 150000 |     160000
  5 | 110000 |     136667
(5 rows)

Example 9: percentage of a total

SQL

SELECT name, salary,
       round(100.0 * salary / sum(salary) OVER (), 1) AS pct_of_payroll
FROM employees
WHERE dept_id = 2
ORDER BY salary DESC;

Output

  name  | salary | pct_of_payroll 
--------+--------+----------------
 Divya  | 160000 |           47.8
 Sameer |  90000 |           26.9
 Pooja  |  85000 |           25.4
(3 rows)

An empty OVER () means one window over all rows that remain after WHERE, here the three Sales employees.

Example 10: FIRST_VALUE and comparing to the top

SQL

SELECT name, salary,
       first_value(salary) OVER (ORDER BY salary DESC) - salary AS below_top
FROM employees
WHERE dept_id = 1
ORDER BY salary DESC, id;

Output

 name  | salary | below_top 
-------+--------+-----------
 Meera | 250000 |         0
 Ravi  | 180000 |     70000
 Kiran | 150000 |    100000
 Neha  | 150000 |    100000
 Arjun | 110000 |    140000
(5 rows)

Example 11: NTILE buckets

ntile(n) splits ordered rows into n buckets of nearly equal size.

SQL

SELECT name, salary, ntile(3) OVER (ORDER BY salary DESC) AS tier
FROM employees
WHERE dept_id IN (1, 2)
ORDER BY salary DESC, id;

Output

  name  | salary | tier 
--------+--------+------
 Meera  | 250000 |    1
 Ravi   | 180000 |    1
 Divya  | 160000 |    1
 Kiran  | 150000 |    2
 Neha   | 150000 |    2
 Arjun  | 110000 |    2
 Sameer |  90000 |    3
 Pooja  |  85000 |    3
(8 rows)

Example 12: default frame trap (RANGE vs ROWS)

With ORDER BY and no frame, the default is RANGE ... CURRENT ROW, which includes all peers (rows with the same sort value). Rows with equal salary share one running total.

SQL

SELECT name, salary,
       sum(salary) OVER (ORDER BY salary) AS range_default,
       sum(salary) OVER (ORDER BY salary ROWS UNBOUNDED PRECEDING) AS rows_frame
FROM employees
WHERE dept_id = 1
ORDER BY salary, id;

Output

 name  | salary | range_default | rows_frame 
-------+--------+---------------+------------
 Arjun | 110000 |        110000 |     110000
 Kiran | 150000 |        410000 |     260000
 Neha  | 150000 |        410000 |     410000
 Ravi  | 180000 |        590000 |     590000
 Meera | 250000 |        840000 |     840000
(5 rows)

Use ROWS when you want a strict row-by-row running total.

Summary

Window functions add a column without removing rows.
row_number numbers, rank leaves gaps after ties, dense_rank does not.
Filter on a window result in an outer query.
With ORDER BY and no frame, peers share the same running value.