Window Functions

GROUP BY is great for totals, but it has a cost: it squashes many rows into one. Often you want both at once, every employee's row and the highest salary in their department, every order and the running total so far. That is what window functions do.

A window function calculates a value over a set of rows related to the current row, called the window, without merging those rows together. MySQL has supported them since version 8.0. In this lesson you will learn OVER and PARTITION BY, rank rows with ROW_NUMBER, RANK and DENSE_RANK, build running totals, and compare a row with the one before it using LAG and LEAD.

OVER: an aggregate that keeps every row

Add OVER (...) after an aggregate function such as SUM() or MAX(), and it becomes a window function. PARTITION BY splits the rows into groups, like GROUP BY, but each row stays in the result:

SELECT first_name, department, salary,
       MAX(salary) OVER (PARTITION BY department) AS top_in_dept,
       SUM(salary) OVER () AS total_payroll
FROM employees
ORDER BY department, salary DESC;

first_name | department  | salary | top_in_dept | total_payroll
-----------+-------------+--------+-------------+--------------
Neha       | Engineering | 180000 | 180000      | 995000
Amit       | Engineering | 120000 | 180000      | 995000
Pooja      | Engineering | 75000  | 180000      | 995000
Karan      | Engineering | 70000  | 180000      | 995000
Rajesh     | Management  | 250000 | 250000      | 995000
Simran     | Sales       | 110000 | 110000      | 995000
Rahul      | Sales       | 45000  | 110000      | 995000
Nisha      | Sales       | 42000  | 110000      | 995000
Deepak     | Support     | 65000  | 65000       | 995000
Tanvi      | Support     | 38000  | 65000       | 995000
(10 rows)

• MAX(salary) OVER (PARTITION BY department) looks only at rows in the same department as the current row.
• SUM(salary) OVER () has an empty window, which means "all rows", so every row shows the full payroll of ₹9,95,000.

All ten employees are still there. With GROUP BY department, you would get only four rows and lose the names. Because the window values sit next to normal columns, you can compare them directly, for example salary - MAX(salary) OVER (PARTITION BY department) shows how far each person is from the top of their department.

ROW_NUMBER, RANK and DENSE_RANK

Ranking functions number the rows in a window. Put ORDER BY inside OVER to say what decides the order. The three functions differ only in how they handle ties:

SELECT c.first_name, COUNT(*) AS orders_placed,
       ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC, c.first_name) AS row_no,
       RANK() OVER (ORDER BY COUNT(*) DESC) AS rank_no,
       DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS dense_no
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name
ORDER BY row_no
LIMIT 6;

first_name | orders_placed | row_no | rank_no | dense_no
-----------+---------------+--------+---------+---------
Aarav      | 3             | 1      | 1       | 1
Priya      | 2             | 2      | 2       | 2
Rohan      | 2             | 3      | 2       | 2
Ananya     | 1             | 4      | 4       | 3
Arjun      | 1             | 5      | 4       | 3
Farhan     | 1             | 6      | 4       | 3
(6 rows)

Priya and Rohan both placed two orders, and that tie shows the difference:

• ROW_NUMBER() always gives 1, 2, 3, 4... with no repeats. For ties, the order is decided by any extra ORDER BY columns (here the first name).
• RANK() gives tied rows the same rank and then skips: 1, 2, 2, 4, like a cricket tournament table where two teams share second place and the next team is fourth.
• DENSE_RANK() gives tied rows the same rank without skipping: 1, 2, 2, 3.

This query also shows that window functions run after GROUP BY: they can rank the grouped rows using COUNT(*).

Top N in each group

A very common task: "the most expensive product in each category". ROW_NUMBER() with PARTITION BY restarts the numbering for every category. You cannot filter on a window function in WHERE, because WHERE runs before window functions are calculated, so wrap the query in a derived table and filter outside:

SELECT category, product_name, price
FROM (
  SELECT category, product_name, price,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS pos
  FROM products
) AS ranked
WHERE pos = 1
ORDER BY price DESC;

category    | product_name                | price
------------+-----------------------------+------
Displays    | 27-inch Monitor             | 15999
Audio       | Noise-Cancelling Headphones | 8999
Storage     | 1TB External SSD            | 6499
Accessories | Graphics Tablet             | 4599
Books       | Python Basics (Book)        | 450
(5 rows)

Change pos = 1 to pos <= 3 to get the top three of each category. A Common Table Expression makes this pattern even easier to read.

Running totals

With ORDER BY inside OVER, an aggregate becomes cumulative: it adds up rows from the start of the window to the current row. This is a running total. The inner query calculates each order's value; the outer query adds them up in date order:

SELECT order_id, order_date, order_total,
       SUM(order_total) OVER (
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM (
  SELECT o.order_id, o.order_date, SUM(oi.quantity * oi.unit_price) AS order_total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status <> 'cancelled'
    AND o.order_date < '2025-04-01'
  GROUP BY o.order_id, o.order_date
) AS t
ORDER BY order_date;

order_id | order_date | order_total | running_total
---------+------------+-------------+--------------
101      | 2025-01-05 | 2147        | 2147
102      | 2025-01-12 | 15999       | 18146
103      | 2025-01-20 | 5398        | 23544
104      | 2025-02-02 | 8999        | 32543
106      | 2025-02-28 | 3948        | 36491
107      | 2025-03-09 | 2499        | 38990
108      | 2025-03-15 | 4298        | 43288
109      | 2025-03-22 | 900         | 44188
(8 rows)

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the frame: from the first row of the window up to this row. When you write only ORDER BY, the default frame treats rows with the same date as one block and gives them the same total, which surprises people. Writing the frame explicitly avoids that. Add PARTITION BY customer_id and each customer gets their own running total that restarts at zero.

LAG and LEAD: looking at neighbouring rows

LAG(column) returns the value from the previous row in the window, and LEAD(column) from the next row. They are perfect for "compared with last time" questions:

SELECT customer_id, order_id, order_date,
       LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_order,
       LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_order
FROM orders
WHERE customer_id IN (1, 2, 3)
ORDER BY customer_id, order_date;

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

The first order of each customer has no previous order, so LAG returns NULL; the last has no next order. In MySQL you could go on to calculate DATEDIFF(order_date, previous_order) to see how many days each customer waits between purchases. LAG(column, 2) looks two rows back, and LAG(column, 1, 0) returns 0 instead of NULL when there is no previous row.

Where window functions fit

SQL runs the parts of a query in this order: FROM and joins, WHERE, GROUP BY, HAVING, then window functions, then ORDER BY and LIMIT. That explains the rules you have seen:

• Window functions can use grouped values such as COUNT(*).
• They cannot appear in WHERE or HAVING; filter on them in an outer query.
• The final ORDER BY can be different from the ORDER BY inside OVER.

Key points

• A window function calculates over related rows but keeps every row; it is written with OVER (...).
• PARTITION BY splits the window into groups; an empty OVER () means all rows.
• ROW_NUMBER never repeats, RANK repeats and skips (1, 2, 2, 4), DENSE_RANK repeats without skipping (1, 2, 2, 3).
• SUM(...) OVER (ORDER BY ...) gives a running total; write the ROWS BETWEEN frame to be explicit.
• LAG and LEAD read the previous and next row's values.
• Window functions run after GROUP BY and cannot be used in WHERE; filter in an outer query.

SELECT first_name, department, salary,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_position,
       ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY department), 1) AS pct_of_dept
FROM employees
ORDER BY department, dept_position;