By the end of this lessonUse window functions to rank rows, calculate running totals and compare a row with the previous or next row without collapsing the result.
The problem GROUP BY cannot solve
GROUP BY answers "what is the average salary per department?" by collapsing each department into one row. But some questions need the individual rows and a group-level number beside them: each employee with their rank inside the department, each order with the running total so far, each month with the change from the month before. Window functions (MySQL 8.0 and later) do exactly that.
A window function looks like an aggregate followed by OVER (...). The OVER clause describes the window, the set of rows the function looks at for the current row.
The simplest window: the whole table
sql
SELECT name, salary, ROUND(AVG(salary) OVER (), 0) AS company_average, salary - ROUND(AVG(salary) OVER (), 0) AS difference
FROM employees
ORDER BY salary DESC
LIMIT 4;result
+-------------+-----------+-----------------+------------+
| name | salary | company_average | difference |
+-------------+-----------+-----------------+------------+
| Meera Iyer | 150000.00 | 89455 | 60545.00 |
| Nina Petrov | 125000.00 | 89455 | 35545.00 |
| Tom Hughes | 110000.00 | 89455 | 20545.00 |
| Omar Haddad | 98000.00 | 89455 | 8545.00 |
+-------------+-----------+-----------------+------------+An empty OVER () means "all rows". Every employee row stays, and each shows the company average next to it.
PARTITION BY: a window per group
PARTITION BY splits the rows into groups, like GROUP BY, but without collapsing them:
sql
SELECT name, department_id, salary,
ROUND(AVG(salary) OVER (PARTITION BY department_id), 0) AS dept_average,
COUNT(*) OVER (PARTITION BY department_id) AS dept_size
FROM employees
WHERE department_id IS NOT NULL
ORDER BY department_id, salary DESC;result
+-------------+---------------+-----------+--------------+-----------+
| name | department_id | salary | dept_average | dept_size |
+-------------+---------------+-----------+--------------+-----------+
| Meera Iyer | 1 | 150000.00 | 97000 | 6 |
| Nina Petrov | 1 | 125000.00 | 97000 | 6 |
| Omar Haddad | 1 | 98000.00 | 97000 | 6 |
| Paula Gomez | 1 | 82000.00 | 97000 | 6 |
| Sam Wright | 1 | 79000.00 | 97000 | 6 |
| Uma Reddy | 1 | 48000.00 | 97000 | 6 |
| Tom Hughes | 2 | 110000.00 | 80000 | 3 |
| Rina Das | 2 | 66000.00 | 80000 | 3 |
| Quinn Baker | 2 | 64000.00 | 80000 | 3 |
| Tara Singh | 3 | 72000.00 | 72000 | 1 |
+-------------+---------------+-----------+--------------+-----------+Ranking rows
Add an ORDER BY inside OVER and ranking functions become available:
| Function | Behaviour with ties |
|---|---|
ROW_NUMBER() |
1, 2, 3, 4: unique numbers, ties broken arbitrarily |
RANK() |
1, 1, 3, 4: ties share a rank, then a gap |
DENSE_RANK() |
1, 1, 2, 3: ties share a rank, no gap |
NTILE(n) |
splits the rows into n equal buckets |
The customers' order counts have ties, which makes the difference visible:
sql
SELECT customer_id, COUNT(*) AS orders,
ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS row_num,
RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS dense_rnk
FROM orders
GROUP BY customer_id
ORDER BY orders DESC, customer_id;result
+-------------+--------+---------+-----+-----------+
| customer_id | orders | row_num | rnk | dense_rnk |
+-------------+--------+---------+-----+-----------+
| 1 | 4 | 1 | 1 | 1 |
| 6 | 3 | 2 | 2 | 2 |
| 2 | 2 | 3 | 3 | 3 |
| 3 | 2 | 4 | 3 | 3 |
| 4 | 2 | 5 | 3 | 3 |
| 8 | 2 | 6 | 3 | 3 |
| 10 | 2 | 7 | 3 | 3 |
| 5 | 1 | 8 | 8 | 4 |
| 7 | 1 | 9 | 8 | 4 |
| 11 | 1 | 10 | 8 | 4 |
+-------------+--------+---------+-----+-----------+Window functions run after GROUP BY, so they can rank the grouped results. Notice that customers 2, 3, 4, 8 and 10 all have two orders and share rank 3, while the next rank is 8 with RANK and 4 with DENSE_RANK.
Top N per group
A classic job: the highest-paid two people in each department. You cannot filter on a window function in WHERE, so compute it in a derived table and filter outside:
sql
SELECT department_id, name, salary, salary_rank
FROM (
SELECT department_id, name, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees
WHERE department_id IS NOT NULL
) AS ranked
WHERE salary_rank <= 2
ORDER BY department_id, salary_rank;result
+---------------+-------------+-----------+-------------+
| department_id | name | salary | salary_rank |
+---------------+-------------+-----------+-------------+
| 1 | Meera Iyer | 150000.00 | 1 |
| 1 | Nina Petrov | 125000.00 | 2 |
| 2 | Tom Hughes | 110000.00 | 1 |
| 2 | Rina Das | 66000.00 | 2 |
| 3 | Tara Singh | 72000.00 | 1 |
+---------------+-------------+-----------+-------------+Running totals
An aggregate with ORDER BY inside OVER becomes a running (cumulative) calculation. First build monthly revenue, then accumulate it:
sql
SELECT month, revenue, SUM(revenue) OVER (ORDER BY month) AS running_revenue
FROM (
SELECT DATE_FORMAT(o.order_date, '%Y-%m') 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 DATE_FORMAT(o.order_date, '%Y-%m')
) AS monthly
ORDER BY month;result
+---------+---------+-----------------+
| month | revenue | running_revenue |
+---------+---------+-----------------+
| 2024-01 | 156.89 | 156.89 |
| 2024-02 | 392.48 | 549.37 |
| 2024-03 | 200.97 | 750.34 |
| 2024-04 | 487.92 | 1238.26 |
| 2024-05 | 223.98 | 1462.24 |
| 2024-06 | 191.35 | 1653.59 |
| 2024-07 | 318.73 | 1972.32 |
+---------+---------+-----------------+By default, with an ORDER BY, the window runs from the first row up to the current one. You can make this explicit or change it with a frame: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW gives a three-month moving window.
sql
SELECT month, revenue, ROUND(AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS three_month_average
FROM (
SELECT DATE_FORMAT(o.order_date, '%Y-%m') 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 DATE_FORMAT(o.order_date, '%Y-%m')
) AS monthly
ORDER BY month;result
+---------+---------+---------------------+
| month | revenue | three_month_average |
+---------+---------+---------------------+
| 2024-01 | 156.89 | 156.89 |
| 2024-02 | 392.48 | 274.69 |
| 2024-03 | 200.97 | 250.11 |
| 2024-04 | 487.92 | 360.46 |
| 2024-05 | 223.98 | 304.29 |
| 2024-06 | 191.35 | 301.08 |
| 2024-07 | 318.73 | 244.69 |
+---------+---------+---------------------+LAG and LEAD: neighbouring rows
LAG(column, n, default) reads the value from n rows earlier, and LEAD from n rows later. They make "change since last time" trivial:
sql
SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS previous_month,
revenue - LAG(revenue) OVER (ORDER BY month) AS change_from_previous
FROM (
SELECT DATE_FORMAT(o.order_date, '%Y-%m') 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 DATE_FORMAT(o.order_date, '%Y-%m')
) AS monthly
ORDER BY month;result
+---------+---------+----------------+----------------------+
| month | revenue | previous_month | change_from_previous |
+---------+---------+----------------+----------------------+
| 2024-01 | 156.89 | NULL | NULL |
| 2024-02 | 392.48 | 156.89 | 235.59 |
| 2024-03 | 200.97 | 392.48 | -191.51 |
| 2024-04 | 487.92 | 200.97 | 286.95 |
| 2024-05 | 223.98 | 487.92 | -263.94 |
| 2024-06 | 191.35 | 223.98 | -32.63 |
| 2024-07 | 318.73 | 191.35 | 127.38 |
+---------+---------+----------------+----------------------+The first month has no previous row, so its previous_month is NULL. FIRST_VALUE, LAST_VALUE and NTH_VALUE pick values from specific positions in the window.
A named window
When several functions share the same window, define it once with WINDOW:
sql
SELECT name, department_id, salary,
RANK() OVER w AS salary_rank,
ROUND(salary / SUM(salary) OVER (PARTITION BY department_id) * 100, 1) AS percent_of_department
FROM employees
WHERE department_id = 2
WINDOW w AS (PARTITION BY department_id ORDER BY salary DESC);result
+-------------+---------------+-----------+-------------+-----------------------+
| name | department_id | salary | salary_rank | percent_of_department |
+-------------+---------------+-----------+-------------+-----------------------+
| Tom Hughes | 2 | 110000.00 | 1 | 45.8 |
| Rina Das | 2 | 66000.00 | 2 | 27.5 |
| Quinn Baker | 2 | 64000.00 | 3 | 26.7 |
+-------------+---------------+-----------+-------------+-----------------------+Things to remember
- Window functions are evaluated after
WHERE,GROUP BYandHAVING, but beforeORDER BYandLIMIT. That is why you cannot use them inWHEREdirectly. - A window function with no
ORDER BYinsideOVERsees the whole partition. With anORDER BYit defaults to a running frame. - Ordering inside
OVERdoes not order the final result. Add a normalORDER BYfor that.
Common mistakes
- Using a window function in
WHERE. Wrap the query in a derived table or CTE. - Forgetting that
RANKleaves gaps after ties. - Expecting
OVER (ORDER BY ...)to sort the output. - Using them on an old server. MySQL 5.7 and earlier do not support them.
- Using unique-less ordering with
ROW_NUMBER, which makes the numbering unstable. Add a tie-breaker.
Exercise
For the products table, show each product's name, category id, price, its rank by price within its category (highest = 1), and the difference between its price and the most expensive price in its category. Then keep only the cheapest product per category.
Show one possible answer
sql
SELECT name, category_id, price,
RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS price_rank,
MAX(price) OVER (PARTITION BY category_id) - price AS below_category_max
FROM products
ORDER BY category_id, price_rank;
SELECT category_id, name, price
FROM (
SELECT category_id, name, price,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price ASC) AS cheapest_first
FROM products
) AS t
WHERE cheapest_first = 1
ORDER BY category_id;result
+-----------------------------+-------------+--------+------------+--------------------+
| name | category_id | price | price_rank | below_category_max |
+-----------------------------+-------------+--------+------------+--------------------+
| Noise-Cancelling Headphones | 1 | 199.99 | 1 | 0.00 |
| Mechanical Keyboard | 1 | 89.50 | 2 | 110.49 |
| USB-C Hub | 1 | 39.00 | 3 | 160.99 |
| Wireless Mouse | 1 | 24.99 | 4 | 175.00 |
| Data Structures Illustrated | 2 | 45.00 | 1 | 0.00 |
| Web Design Patterns | 2 | 34.50 | 2 | 10.50 |
| SQL Basics Handbook | 2 | 29.95 | 3 | 15.05 |
| Standing Desk Mat | 3 | 55.00 | 1 | 0.00 |
| Desk Lamp | 3 | 32.00 | 2 | 23.00 |
| Ceramic Mug Set | 3 | 18.75 | 3 | 36.25 |
| Yoga Mat | 4 | 27.50 | 1 | 0.00 |
| Resistance Bands | 4 | 14.99 | 2 | 12.51 |
| Water Bottle | 4 | 12.00 | 3 | 15.50 |
| Building Blocks Set | 5 | 49.99 | 1 | 0.00 |
| Puzzle 1000 Pieces | 5 | 19.99 | 2 | 30.00 |
+-----------------------------+-------------+--------+------------+--------------------+
+-------------+---------------------+-------+
| category_id | name | price |
+-------------+---------------------+-------+
| 1 | Wireless Mouse | 24.99 |
| 2 | SQL Basics Handbook | 29.95 |
| 3 | Ceramic Mug Set | 18.75 |
| 4 | Water Bottle | 12.00 |
| 5 | Puzzle 1000 Pieces | 19.99 |
+-------------+---------------------+-------+Key points
- A window function calculates a value across a set of related rows but, unlike GROUP BY, keeps every row in the result.
- OVER (PARTITION BY ... ORDER BY ...) defines the window: PARTITION BY splits rows into groups, ORDER BY orders them inside each group.
- ROW_NUMBER, RANK and DENSE_RANK number rows; they differ in how they treat ties. NTILE splits rows into buckets.
- LAG and LEAD read the previous and next row; SUM(...) OVER (ORDER BY ...) gives a running total. Window functions need MySQL 8.0 or newer.