With GROUP BY we can find the average salary of each department. But GROUP BY collapses the rows: you get one row per department and lose the individual employees. What if you want to see every employee and, right next to them, their department's average? Or every order with a running total of revenue so far? Or each order next to the customer's previous order?
In the previous chapter we solved such problems by computing the group values in a CTE and joining them back. Window functions do it directly, in one step, without a join.
A window function performs a calculation across a set of rows related to the current row (its "window"), and returns a value for every row, without collapsing the rows.
Window functions are among the most asked topics in SQL interviews and are used constantly in data analysis. MySQL supports them from version 8.0. All examples use the shared company_db database, with real MySQL 8.4 output.
GROUP BY vs Window Function
Here is the GROUP BY way:
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
WHERE dept_id IS NOT NULL
GROUP BY dept_id
ORDER BY dept_id;+---------+---------------+
| dept_id | avg_salary |
+---------+---------------+
| 10 | 113333.333333 |
| 20 | 76666.666667 |
| 30 | 59000.000000 |
| 40 | 82000.000000 |
+---------+---------------+
4 rows in set (0.00 sec)Nine employees went in, four rows came out. Now the same average as a window function:
SELECT name, dept_id, salary,
ROUND(AVG(salary) OVER (PARTITION BY dept_id), 2) AS dept_avg,
ROUND(salary - AVG(salary) OVER (PARTITION BY dept_id), 2) AS diff_from_avg
FROM employees
WHERE dept_id IS NOT NULL
ORDER BY dept_id, salary DESC;+--------------+---------+-----------+-----------+---------------+
| name | dept_id | salary | dept_avg | diff_from_avg |
+--------------+---------+-----------+-----------+---------------+
| Aarav Sharma | 10 | 150000.00 | 113333.33 | 36666.67 |
| Priya Iyer | 10 | 95000.00 | 113333.33 | -18333.33 |
| Rohan Mehta | 10 | 95000.00 | 113333.33 | -18333.33 |
| Sneha Kapoor | 20 | 110000.00 | 76666.67 | 33333.33 |
| Vikram Singh | 20 | 62000.00 | 76666.67 | -14666.67 |
| Ananya Das | 20 | 58000.00 | 76666.67 | -18666.67 |
| Karan Patel | 30 | 70000.00 | 59000.00 | 11000.00 |
| Meera Nair | 30 | 48000.00 | 59000.00 | -11000.00 |
| Arjun Reddy | 40 | 82000.00 | 82000.00 | 0.00 |
+--------------+---------+-----------+-----------+---------------+
9 rows in set (0.00 sec)All 9 rows stay. Each row gets the average of its department, and we can even do arithmetic with it in the same SELECT. The magic is the OVER (...) clause: it turns the ordinary aggregate AVG() into a window function.
GROUP BY: Window function:
Aarav 10 150000 \ Aarav 10 150000 | 113333.33
Priya 10 95000 > 10 113333.33 Priya 10 95000 | 113333.33
Rohan 10 95000 / Rohan 10 95000 | 113333.33
Sneha 20 110000 \ Sneha 20 110000 | 76666.67
... > 20 76666.67 ...
many rows -> one row per group every row kept, group value attachedWindow Function Syntax
function_name(expression) OVER (
[PARTITION BY column, ...] -- split rows into groups (windows)
[ORDER BY column [ASC|DESC]] -- order rows inside each window
[frame clause] -- which rows of the window to use (ROWS/RANGE ...)
)Part | Meaning | If omitted |
|---|---|---|
| Like GROUP BY for the window: the calculation restarts for each group | The whole result is one window |
| The order of rows inside the window (needed for running totals, LAG, LEAD) | No order; aggregates use the whole partition |
Frame ( | A sliding subset of the partition relative to the current row | Depends on whether ORDER BY is present (explained below) |
Window functions come in three families:
Aggregate window functions:
SUM,AVG,COUNT,MIN,MAXwithOVER.Value (navigation) functions:
LAG,LEAD,FIRST_VALUE,LAST_VALUE,NTH_VALUE.Ranking functions:
ROW_NUMBER,RANK,DENSE_RANKand friends. They are so important that they get their own chapter next.
OVER (): The Whole Result as One Window
With empty parentheses, the window is every row of the result. What share of the total payroll does each employee earn?
SELECT name, salary,
SUM(salary) OVER () AS total_payroll,
ROUND(100 * salary / SUM(salary) OVER (), 2) AS pct_of_payroll
FROM employees
ORDER BY salary DESC;+--------------+-----------+---------------+----------------+
| name | salary | total_payroll | pct_of_payroll |
+--------------+-----------+---------------+----------------+
| Aarav Sharma | 150000.00 | 825000.00 | 18.18 |
| Sneha Kapoor | 110000.00 | 825000.00 | 13.33 |
| Priya Iyer | 95000.00 | 825000.00 | 11.52 |
| Rohan Mehta | 95000.00 | 825000.00 | 11.52 |
| Arjun Reddy | 82000.00 | 825000.00 | 9.94 |
| Karan Patel | 70000.00 | 825000.00 | 8.48 |
| Vikram Singh | 62000.00 | 825000.00 | 7.52 |
| Ananya Das | 58000.00 | 825000.00 | 7.03 |
| Ishita Gupta | 55000.00 | 825000.00 | 6.67 |
| Meera Nair | 48000.00 | 825000.00 | 5.82 |
+--------------+-----------+---------------+----------------+
10 rows in set (0.00 sec)Without window functions you would need a subquery for the total. Aarav: 150000 / 825000 × 100 = 18.18%.
PARTITION BY: One Window per Group
PARTITION BY splits the rows into groups, and the function is computed separately for each group. Several window functions can sit in one SELECT:
SELECT name, dept_id,
COUNT(*) OVER (PARTITION BY dept_id) AS dept_size,
MAX(salary) OVER (PARTITION BY dept_id) AS dept_max
FROM employees
ORDER BY dept_id, name;+--------------+---------+-----------+-----------+
| name | dept_id | dept_size | dept_max |
+--------------+---------+-----------+-----------+
| Ishita Gupta | NULL | 1 | 55000.00 |
| Aarav Sharma | 10 | 3 | 150000.00 |
| Priya Iyer | 10 | 3 | 150000.00 |
| Rohan Mehta | 10 | 3 | 150000.00 |
| Ananya Das | 20 | 3 | 110000.00 |
| Sneha Kapoor | 20 | 3 | 110000.00 |
| Vikram Singh | 20 | 3 | 110000.00 |
| Karan Patel | 30 | 2 | 70000.00 |
| Meera Nair | 30 | 2 | 70000.00 |
| Arjun Reddy | 40 | 1 | 82000.00 |
+--------------+---------+-----------+-----------+
10 rows in set (0.00 sec)Like GROUP BY, PARTITION BY puts all NULLs together, so Ishita (no department) forms her own partition of size 1.
Naming a Window: the WINDOW Clause
When several functions share the same window, define it once with a WINDOW clause (after WHERE/GROUP BY/HAVING, before ORDER BY) and refer to it by name:
SELECT name, dept_id, salary,
MIN(salary) OVER w AS dept_min,
MAX(salary) OVER w AS dept_max,
ROUND(AVG(salary) OVER w, 2) AS dept_avg
FROM employees
WHERE dept_id IS NOT NULL
WINDOW w AS (PARTITION BY dept_id)
ORDER BY dept_id, salary DESC;+--------------+---------+-----------+----------+-----------+-----------+
| name | dept_id | salary | dept_min | dept_max | dept_avg |
+--------------+---------+-----------+----------+-----------+-----------+
| Aarav Sharma | 10 | 150000.00 | 95000.00 | 150000.00 | 113333.33 |
| Priya Iyer | 10 | 95000.00 | 95000.00 | 150000.00 | 113333.33 |
| Rohan Mehta | 10 | 95000.00 | 95000.00 | 150000.00 | 113333.33 |
| Sneha Kapoor | 20 | 110000.00 | 58000.00 | 110000.00 | 76666.67 |
| Vikram Singh | 20 | 62000.00 | 58000.00 | 110000.00 | 76666.67 |
| Ananya Das | 20 | 58000.00 | 58000.00 | 110000.00 | 76666.67 |
| Karan Patel | 30 | 70000.00 | 48000.00 | 70000.00 | 59000.00 |
| Meera Nair | 30 | 48000.00 | 48000.00 | 70000.00 | 59000.00 |
| Arjun Reddy | 40 | 82000.00 | 82000.00 | 82000.00 | 82000.00 |
+--------------+---------+-----------+----------+-----------+-----------+
9 rows in set (0.00 sec)ORDER BY Inside OVER: Running Totals
Adding ORDER BY inside OVER changes the meaning of an aggregate: instead of the whole partition, it covers the rows from the start up to the current row. That gives a running (cumulative) total:
SELECT order_id, order_date, amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders
WHERE status <> 'Cancelled'
ORDER BY order_date;+----------+------------+---------+---------------+
| order_id | order_date | amount | running_total |
+----------+------------+---------+---------------+
| 1001 | 2024-01-15 | 2500.00 | 2500.00 |
| 1002 | 2024-01-20 | 4800.00 | 7300.00 |
| 1004 | 2024-02-14 | 7300.00 | 14600.00 |
| 1005 | 2024-03-01 | 950.00 | 15550.00 |
| 1006 | 2024-03-11 | 3100.00 | 18650.00 |
| 1007 | 2024-03-28 | 5600.00 | 24250.00 |
| 1008 | 2024-04-02 | 800.00 | 25050.00 |
+----------+------------+---------+---------------+
7 rows in set (0.01 sec)Trace: 2500, then 2500 + 4800 = 7300, then 7300 + 7300 = 14600, and so on. The last row shows the grand total, 25050. Note that the WHERE clause ran first, so the cancelled order 1003 is not part of any window.
Running Total per Group
Combine PARTITION BY and ORDER BY and the running total restarts for each customer. A running COUNT(*) numbers each customer's orders:
SELECT customer_id, order_id, order_date, amount,
SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS customer_running_total,
COUNT(*) OVER (PARTITION BY customer_id ORDER BY order_date) AS order_no
FROM orders
WHERE customer_id IS NOT NULL
ORDER BY customer_id, order_date;+-------------+----------+------------+---------+------------------------+----------+
| customer_id | order_id | order_date | amount | customer_running_total | order_no |
+-------------+----------+------------+---------+------------------------+----------+
| 101 | 1001 | 2024-01-15 | 2500.00 | 2500.00 | 1 |
| 101 | 1003 | 2024-02-02 | 1200.00 | 3700.00 | 2 |
| 101 | 1007 | 2024-03-28 | 5600.00 | 9300.00 | 3 |
| 102 | 1002 | 2024-01-20 | 4800.00 | 4800.00 | 1 |
| 102 | 1006 | 2024-03-11 | 3100.00 | 7900.00 | 2 |
| 103 | 1004 | 2024-02-14 | 7300.00 | 7300.00 | 1 |
| 104 | 1005 | 2024-03-01 | 950.00 | 950.00 | 1 |
+-------------+----------+------------+---------+------------------------+----------+
7 rows in set (0.00 sec)At customer 102 the total starts again from 4800.
Window Frames: ROWS vs RANGE
The frame is the exact set of rows, inside the partition, that the function looks at for the current row. You can write it explicitly:
ROWS BETWEEN start AND end
RANGE BETWEEN start AND end
-- start / end can be:
UNBOUNDED PRECEDING -- the first row of the partition
n PRECEDING -- n rows before the current row
CURRENT ROW
n FOLLOWING -- n rows after the current row
UNBOUNDED FOLLOWING -- the last row of the partitionWhen you do not write a frame, MySQL uses a default:
OVER clause | Default frame |
|---|---|
No ORDER BY | The whole partition |
With ORDER BY |
|
The Difference Shows Up With Ties
ROWScounts physical rows: "up to and including this row".RANGEworks on values: "up to and including all rows with the same ORDER BY value as this row" (called its peers).
Priya and Rohan both earn 95000. Watch what happens to a running total ordered by salary:
SELECT name, salary,
SUM(salary) OVER (ORDER BY salary) AS range_default,
SUM(salary) OVER (ORDER BY salary
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame
FROM employees
ORDER BY salary, name;+--------------+-----------+---------------+------------+
| name | salary | range_default | rows_frame |
+--------------+-----------+---------------+------------+
| Meera Nair | 48000.00 | 48000.00 | 48000.00 |
| Ishita Gupta | 55000.00 | 103000.00 | 103000.00 |
| Ananya Das | 58000.00 | 161000.00 | 161000.00 |
| Vikram Singh | 62000.00 | 223000.00 | 223000.00 |
| Karan Patel | 70000.00 | 293000.00 | 293000.00 |
| Arjun Reddy | 82000.00 | 375000.00 | 375000.00 |
| Priya Iyer | 95000.00 | 565000.00 | 470000.00 |
| Rohan Mehta | 95000.00 | 565000.00 | 565000.00 |
| Sneha Kapoor | 110000.00 | 675000.00 | 675000.00 |
| Aarav Sharma | 150000.00 | 825000.00 | 825000.00 |
+--------------+-----------+---------------+------------+
10 rows in set (0.00 sec)With the default RANGE frame, Priya's total already includes Rohan's salary (375000 + 95000 + 95000 = 565000), because Rohan is her peer. With ROWS, Priya gets 375000 + 95000 = 470000 and Rohan gets 565000. Which of the two tied rows comes first in the ROWS version is not guaranteed; add a tie-breaker (for example ORDER BY salary, emp_id) if it matters.
For running totals, prefer an explicit ROWS frame and a unique ORDER BY. Then each row's total is predictable even when values repeat.
Moving Averages and Sliding Windows
A frame with a fixed size slides along with the current row. A 3-order moving average (current order and the two before it), and the sum of each order with its immediate neighbours:
SELECT order_id, order_date, amount,
ROUND(AVG(amount) OVER (ORDER BY order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3,
SUM(amount) OVER (ORDER BY order_date
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_neighbours
FROM orders
ORDER BY order_date;+----------+------------+---------+--------------+----------------+
| order_id | order_date | amount | moving_avg_3 | sum_neighbours |
+----------+------------+---------+--------------+----------------+
| 1001 | 2024-01-15 | 2500.00 | 2500.00 | 7300.00 |
| 1002 | 2024-01-20 | 4800.00 | 3650.00 | 8500.00 |
| 1003 | 2024-02-02 | 1200.00 | 2833.33 | 13300.00 |
| 1004 | 2024-02-14 | 7300.00 | 4433.33 | 9450.00 |
| 1005 | 2024-03-01 | 950.00 | 3150.00 | 11350.00 |
| 1006 | 2024-03-11 | 3100.00 | 3783.33 | 9650.00 |
| 1007 | 2024-03-28 | 5600.00 | 3216.67 | 9500.00 |
| 1008 | 2024-04-02 | 800.00 | 3166.67 | 6400.00 |
+----------+------------+---------+--------------+----------------+
8 rows in set (0.00 sec)Step by step for a few rows:
order 1001: avg(2500) = 2500.00 (no earlier rows yet)
order 1002: avg(2500, 4800) = 3650.00
order 1003: avg(2500, 4800, 1200) = 2833.33
order 1004: avg(4800, 1200, 7300) = 4433.33 (window slid forward)
sum_neighbours for 1001: 2500 + 4800 = 7300 (no row before)
sum_neighbours for 1002: 2500 + 4800 + 1200 = 8500
sum_neighbours for 1008: 5600 + 800 = 6400 (no row after)At the edges the frame simply has fewer rows. Moving averages are used everywhere to smooth noisy data, like "7-day average of daily sales".
LAG and LEAD: Looking at Other Rows
LAG(expr) returns the value of expr from the previous row in the window; LEAD(expr) from the next row. They need an ORDER BY to know what "previous" means.
For each customer's order, show the previous order's amount and date, the days since that previous order, and the date of the next one:
SELECT customer_id, order_id, order_date, amount,
LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amount,
LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_date,
DATEDIFF(order_date,
LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)) AS days_since_prev,
LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_date
FROM orders
WHERE customer_id IS NOT NULL
ORDER BY customer_id, order_date;+-------------+----------+------------+---------+-------------+------------+-----------------+------------+
| customer_id | order_id | order_date | amount | prev_amount | prev_date | days_since_prev | next_date |
+-------------+----------+------------+---------+-------------+------------+-----------------+------------+
| 101 | 1001 | 2024-01-15 | 2500.00 | NULL | NULL | NULL | 2024-02-02 |
| 101 | 1003 | 2024-02-02 | 1200.00 | 2500.00 | 2024-01-15 | 18 | 2024-03-28 |
| 101 | 1007 | 2024-03-28 | 5600.00 | 1200.00 | 2024-02-02 | 55 | NULL |
| 102 | 1002 | 2024-01-20 | 4800.00 | NULL | NULL | NULL | 2024-03-11 |
| 102 | 1006 | 2024-03-11 | 3100.00 | 4800.00 | 2024-01-20 | 51 | NULL |
| 103 | 1004 | 2024-02-14 | 7300.00 | NULL | NULL | NULL | NULL |
| 104 | 1005 | 2024-03-01 | 950.00 | NULL | NULL | NULL | NULL |
+-------------+----------+------------+---------+-------------+------------+-----------------+------------+
7 rows in set (0.00 sec)The first order of each customer has no previous row, so LAG gives NULL; the last has no next row, so LEAD gives NULL. "Days between consecutive orders" like this is a very common analytics and interview question.
The full form is LAG(expr, offset, default): offset says how many rows back (default 1), and default replaces the NULL when no such row exists:
SELECT order_id, order_date, amount,
LAG(amount, 1, 0) OVER (ORDER BY order_date) AS prev_or_zero,
LAG(amount, 2) OVER (ORDER BY order_date) AS two_back
FROM orders
ORDER BY order_date
LIMIT 4;+----------+------------+---------+--------------+----------+
| order_id | order_date | amount | prev_or_zero | two_back |
+----------+------------+---------+--------------+----------+
| 1001 | 2024-01-15 | 2500.00 | 0.00 | NULL |
| 1002 | 2024-01-20 | 4800.00 | 2500.00 | NULL |
| 1003 | 2024-02-02 | 1200.00 | 4800.00 | 2500.00 |
| 1004 | 2024-02-14 | 7300.00 | 1200.00 | 4800.00 |
+----------+------------+---------+--------------+----------+
4 rows in set (0.00 sec)Notice that LIMIT 4 did not affect the window calculations: window functions are computed on the full result first, and LIMIT cuts the output afterwards.
Month-over-Month Change, the Short Way
In the CTE chapter we compared each month with the previous one by joining a CTE to itself. With LAG it is simpler:
WITH monthly AS (
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(amount) AS revenue
FROM orders
WHERE status <> 'Cancelled'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS change_amt
FROM monthly
ORDER BY month;+---------+---------+--------------+------------+
| month | revenue | prev_revenue | change_amt |
+---------+---------+--------------+------------+
| 2024-01 | 7300.00 | NULL | NULL |
| 2024-02 | 7300.00 | 7300.00 | 0.00 |
| 2024-03 | 9650.00 | 7300.00 | 2350.00 |
| 2024-04 | 800.00 | 9650.00 | -8850.00 |
+---------+---------+--------------+------------+
4 rows in set (0.00 sec)Same numbers as the self-join version. One caution: LAG looks at the previous row, not the previous calendar month. If a month were missing from monthly, LAG would compare with an older month. Fill gaps first (with the recursive month series from the CTE chapter) if that matters.
FIRST_VALUE, LAST_VALUE and NTH_VALUE
These return the value from the first, last, or n-th row of the window frame. Who is the top earner and the lowest earner in each department?
SELECT name, dept_id, salary,
FIRST_VALUE(name) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS top_earner,
LAST_VALUE(name) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS last_value_default,
LAST_VALUE(name) OVER (PARTITION BY dept_id ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS lowest_earner
FROM employees
WHERE dept_id IN (10, 20)
ORDER BY dept_id, salary DESC, name;+--------------+---------+-----------+--------------+--------------------+---------------+
| name | dept_id | salary | top_earner | last_value_default | lowest_earner |
+--------------+---------+-----------+--------------+--------------------+---------------+
| Aarav Sharma | 10 | 150000.00 | Aarav Sharma | Aarav Sharma | Rohan Mehta |
| Priya Iyer | 10 | 95000.00 | Aarav Sharma | Rohan Mehta | Rohan Mehta |
| Rohan Mehta | 10 | 95000.00 | Aarav Sharma | Rohan Mehta | Rohan Mehta |
| Sneha Kapoor | 20 | 110000.00 | Sneha Kapoor | Sneha Kapoor | Ananya Das |
| Vikram Singh | 20 | 62000.00 | Sneha Kapoor | Vikram Singh | Ananya Das |
| Ananya Das | 20 | 58000.00 | Sneha Kapoor | Ananya Das | Ananya Das |
+--------------+---------+-----------+--------------+--------------------+---------------+
6 rows in set (0.00 sec)FIRST_VALUE works as expected. But look at last_value_default: for Vikram it says "Vikram Singh", not the lowest earner. This is the famous LAST_VALUE trap. With ORDER BY, the default frame ends at the current row (and its peers), so the "last" row of the frame is the current row itself. For Priya it shows Rohan only because Rohan is her peer (same salary). To really look at the whole partition, write the frame ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, as in lowest_earner.
In Engineering, Priya and Rohan tie for lowest; the function returns whichever of them is last in the window's order, which is not guaranteed. A tie-breaker in the ORDER BY makes it deterministic.
NTH_VALUE(expr, n) returns the value from the n-th row of the frame. The second-highest salary in the company:
SELECT name, salary,
NTH_VALUE(salary, 2) OVER (ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS second_value
FROM employees
ORDER BY salary DESC
LIMIT 3;+--------------+-----------+--------------+
| name | salary | second_value |
+--------------+-----------+--------------+
| Aarav Sharma | 150000.00 | 110000.00 |
| Sneha Kapoor | 110000.00 | 110000.00 |
| Priya Iyer | 95000.00 | 110000.00 |
+--------------+-----------+--------------+
3 rows in set (0.00 sec)(For "n-th highest" questions where ties matter, the ranking functions of the next chapter are usually the better tool.)
Where Window Functions Run: Logical Query Order
Window functions are evaluated after FROM, WHERE, GROUP BY and HAVING, and before ORDER BY and LIMIT:
FROM / JOIN -> WHERE -> GROUP BY -> HAVING -> window functions -> SELECT list -> DISTINCT -> ORDER BY -> LIMITTwo consequences follow.
1. You Cannot Use a Window Function in WHERE
Trying to keep employees earning above their department average directly:
SELECT name, salary, AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM employees
WHERE salary > AVG(salary) OVER (PARTITION BY dept_id);ERROR 3593 (HY000): You cannot use the window function 'avg' in this context.'WHERE runs before the window values exist. (The same goes for HAVING and GROUP BY.) The fix is to compute the window value in a CTE or derived table, then filter outside:
WITH with_avg AS (
SELECT name, dept_id, salary,
AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM employees
WHERE dept_id IS NOT NULL
)
SELECT name, dept_id, salary, ROUND(dept_avg, 2) AS dept_avg
FROM with_avg
WHERE salary > dept_avg
ORDER BY dept_id;+--------------+---------+-----------+-----------+
| name | dept_id | salary | dept_avg |
+--------------+---------+-----------+-----------+
| Aarav Sharma | 10 | 150000.00 | 113333.33 |
| Sneha Kapoor | 20 | 110000.00 | 76666.67 |
| Karan Patel | 30 | 70000.00 | 59000.00 |
+--------------+---------+-----------+-----------+
3 rows in set (0.00 sec)The same answer as the first example of the CTE chapter, but without a join. This "compute in a CTE, filter outside" pattern is the standard way to filter on any window function, and you will use it constantly with ranking functions in the next chapter.
2. Window Functions Can Work on Grouped Results
Because they run after GROUP BY, a window function can take an aggregate as its input. Each department's share of the total payroll:
SELECT dept_id, SUM(salary) AS dept_total,
ROUND(100 * SUM(salary) / SUM(SUM(salary)) OVER (), 2) AS pct_of_company
FROM employees
GROUP BY dept_id
ORDER BY dept_total DESC;+---------+------------+----------------+
| dept_id | dept_total | pct_of_company |
+---------+------------+----------------+
| 10 | 340000.00 | 41.21 |
| 20 | 230000.00 | 27.88 |
| 30 | 118000.00 | 14.30 |
| 40 | 82000.00 | 9.94 |
| NULL | 55000.00 | 6.67 |
+---------+------------+----------------+
5 rows in set (0.00 sec)SUM(SUM(salary)) OVER () looks strange but reads naturally from the inside: the inner SUM(salary) is the GROUP BY total per department; the outer SUM(...) OVER () adds those totals across all the grouped rows (825000).
Window Functions vs Alternatives
Task | Without window functions | With window functions |
|---|---|---|
Group value next to each row | GROUP BY in a CTE/subquery + join back |
|
Running total | Correlated subquery summing all earlier rows (slow: re-scans for every row) |
|
Previous / next row | Self join on "previous date", tricky with gaps and ties |
|
Percentage of total | Scalar subquery for the total |
|
Common Mistakes
Mistake | What happens | Fix |
|---|---|---|
Using a window function in WHERE / HAVING | Error 3593 | Compute it in a CTE, filter in the outer query |
Forgetting ORDER BY in OVER for running totals | Every row shows the partition total |
|
Relying on the default RANGE frame with ties | Tied rows get the same running total | Use |
| Returns the current row (or its last peer) | Frame |
Expecting ORDER BY inside OVER to sort the output | Output order not guaranteed | Add a final ORDER BY for the query |
Confusing PARTITION BY with GROUP BY | Expecting fewer rows | PARTITION BY never removes rows |
Assuming LAG means "previous month/day" | Wrong comparison when periods are missing | Fill gaps first, or compare dates explicitly |
Interview Questions
What is a window function?
A function that computes a value over a set of rows related to the current row (defined by OVER) and returns a value for every row, without collapsing rows like GROUP BY does.
What is the difference between GROUP BY and PARTITION BY?
GROUP BY returns one row per group. PARTITION BY (inside OVER) splits rows into groups for the calculation but keeps every row in the output.
How do you calculate a running total in SQL?
SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), adding PARTITION BY to restart it per group.
What is the difference between ROWS and RANGE?
ROWS defines the frame by physical row positions. RANGE defines it by ORDER BY values, so rows with equal values (peers) are always included together. They differ only when there are ties.
What do LAG and LEAD do?
LAG returns a value from a previous row and LEAD from a following row in the window order, optionally with an offset and a default for when no such row exists.
Why does LAST_VALUE often return the current row's value?
With ORDER BY, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which ends at the current row. Use a frame ending at UNBOUNDED FOLLOWING.
Can you use a window function in a WHERE clause?
No. Window functions are evaluated after WHERE, GROUP BY and HAVING. Compute them in a CTE or subquery and filter in the outer query.
How do you find the difference between each order and the customer's previous order?
amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date), or with dates, DATEDIFF(order_date, LAG(order_date) OVER (...)).
What is the default frame when OVER has no ORDER BY?
The entire partition, so aggregates return the partition-wide total, average, etc.
Summary
A window function = function +
OVER (...). It keeps every row and adds a computed value.OVER ()uses all rows;PARTITION BYcomputes per group;ORDER BYinside OVER makes aggregates cumulative.Frames (
ROWS/RANGE BETWEEN ...) choose exactly which rows count: running totals, moving averages, sliding sums.Default frame with ORDER BY is RANGE up to the current row, which treats ties as one step and breaks
LAST_VALUE.LAG/LEADread previous/next rows;FIRST_VALUE,LAST_VALUE,NTH_VALUEread positions in the frame.Window functions run after WHERE/GROUP BY/HAVING and before ORDER BY/LIMIT: filter on them through a CTE, and you can window over aggregates.
The
WINDOW w AS (...)clause names a window to reuse it.
One family is still waiting: functions that number and rank rows. In the next chapter, Ranking Functions (ROW_NUMBER, RANK, DENSE_RANK), we will use them to solve classic interview problems like "top 3 earners per department" and "N-th highest salary".