Chapter 30 of 35

Window Functions

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 attached

Window 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

PARTITION BY

Like GROUP BY for the window: the calculation restarts for each group

The whole result is one window

ORDER BY

The order of rows inside the window (needed for running totals, LAG, LEAD)

No order; aggregates use the whole partition

Frame (ROWS/RANGE BETWEEN ...)

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, MAX with OVER.

  • Value (navigation) functions: LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE.

  • Ranking functions: ROW_NUMBER, RANK, DENSE_RANK and 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 partition

When you do not write a frame, MySQL uses a default:

OVER clause

Default frame

No ORDER BY

The whole partition

With ORDER BY

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

The Difference Shows Up With Ties

  • ROWS counts physical rows: "up to and including this row".

  • RANGE works 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  ->  LIMIT

Two 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

AVG(x) OVER (PARTITION BY g)

Running total

Correlated subquery summing all earlier rows (slow: re-scans for every row)

SUM(x) OVER (ORDER BY d)

Previous / next row

Self join on "previous date", tricky with gaps and ties

LAG(x) / LEAD(x)

Percentage of total

Scalar subquery for the total

x / SUM(x) OVER ()


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

OVER (ORDER BY ...)

Relying on the default RANGE frame with ties

Tied rows get the same running total

Use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW with a unique ORDER BY

LAST_VALUE with the default frame

Returns the current row (or its last peer)

Frame ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

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 BY computes per group; ORDER BY inside 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/LEAD read previous/next rows; FIRST_VALUE, LAST_VALUE, NTH_VALUE read 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".