Window functions

OVER, PARTITION BY, ROW_NUMBER, RANK, running totals, LAG/LEAD and frame clauses.

Window functions (MySQL 8.0+) perform calculations across a set of rows that are related to the current row, called its window, without collapsing rows the way GROUP BY does. They make rankings, running totals, moving averages, "top N per group" and comparisons with the previous row easy. Before MySQL 8, these needed ugly self joins or session-variable hacks.

Sample data#

SQL

CREATE TABLE sales (
    id      INT PRIMARY KEY,
    rep     VARCHAR(10) NOT NULL,
    region  VARCHAR(10) NOT NULL,
    sold_on DATE NOT NULL,
    amount  INT NOT NULL
);
INSERT INTO sales VALUES
    (1, 'Asha',  'North', '2026-01-03', 500),
    (2, 'Rohan', 'North', '2026-01-04', 300),
    (3, 'Asha',  'North', '2026-01-08', 200),
    (4, 'Meera', 'South', '2026-01-02', 700),
    (5, 'Karan', 'South', '2026-01-05', 300),
    (6, 'Meera', 'South', '2026-01-09', 300),
    (7, 'Rohan', 'North', '2026-01-10', 500);

The OVER clause#

Any aggregate becomes a window function when you add OVER (...):

SQL

SELECT id, rep, region, amount,
       SUM(amount) OVER ()                     AS grand_total,
       SUM(amount) OVER (PARTITION BY region)  AS region_total,
       ROUND(100 * amount / SUM(amount) OVER (PARTITION BY region), 1) AS pct_of_region
FROM sales
ORDER BY region, id;

Output

+----+-------+--------+--------+-------------+--------------+---------------+
| id | rep   | region | amount | grand_total | region_total | pct_of_region |
+----+-------+--------+--------+-------------+--------------+---------------+
|  1 | Asha  | North  |    500 |        2800 |         1500 |          33.3 |
|  2 | Rohan | North  |    300 |        2800 |         1500 |          20.0 |
|  3 | Asha  | North  |    200 |        2800 |         1500 |          13.3 |
|  7 | Rohan | North  |    500 |        2800 |         1500 |          33.3 |
|  4 | Meera | South  |    700 |        2800 |         1300 |          53.8 |
|  5 | Karan | South  |    300 |        2800 |         1300 |          23.1 |
|  6 | Meera | South  |    300 |        2800 |         1300 |          23.1 |
+----+-------+--------+--------+-------------+--------------+---------------+

OVER () means the window is all rows.
PARTITION BY region splits rows into groups, like GROUP BY, but each row keeps its identity and sees its own group's total.

Ranking: ROW_NUMBER, RANK, DENSE_RANK#

SQL

SELECT rep, region, amount,
       ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num,
       RANK()       OVER (ORDER BY amount DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY amount DESC) AS dense
FROM sales
ORDER BY amount DESC, id;

Output

+-------+--------+--------+---------+-----+-------+
| rep   | region | amount | row_num | rnk | dense |
+-------+--------+--------+---------+-----+-------+
| Meera | South  |    700 |       1 |   1 |     1 |
| Asha  | North  |    500 |       2 |   2 |     2 |
| Rohan | North  |    500 |       3 |   2 |     2 |
| Rohan | North  |    300 |       4 |   4 |     3 |
| Karan | South  |    300 |       5 |   4 |     3 |
| Meera | South  |    300 |       6 |   4 |     3 |
| Asha  | North  |    200 |       7 |   7 |     4 |
+-------+--------+--------+---------+-----+-------+

Function	Ties	Example sequence
`ROW_NUMBER()`	arbitrary but unique	1, 2, 3, 4
`RANK()`	same rank, then a gap	1, 2, 2, 4
`DENSE_RANK()`	same rank, no gap	1, 2, 2, 3

ROW_NUMBER breaks ties arbitrarily. Add a tie-breaker (ORDER BY amount DESC, id) when you need repeatable results. Other ranking helpers include NTILE(n) (split into n buckets), PERCENT_RANK() and CUME_DIST().

Top N per group#

"The biggest sale in each region" is a classic. Window functions are computed after WHERE, so compute the rank in a CTE and filter outside:

SQL

WITH ranked AS (
    SELECT rep, region, amount,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC, id) AS rn
    FROM sales
)
SELECT region, rep, amount
FROM ranked
WHERE rn = 1;

Output

+--------+-------+--------+
| region | rep   | amount |
+--------+-------+--------+
| North  | Asha  |    500 |
| South  | Meera |    700 |
+--------+-------+--------+

Change rn = 1 to rn <= 3 for the top three per group. Use RANK() if ties should all be included.

Running totals and moving averages#

With ORDER BY inside OVER, an aggregate becomes cumulative:

SQL

SELECT sold_on, amount,
       SUM(amount) OVER (ORDER BY sold_on) AS running_total,
       ROUND(AVG(amount) OVER (ORDER BY sold_on
                               ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS moving_avg_3
FROM sales
ORDER BY sold_on;

Output

+------------+--------+---------------+--------------+
| sold_on    | amount | running_total | moving_avg_3 |
+------------+--------+---------------+--------------+
| 2026-01-02 |    700 |           700 |        700.0 |
| 2026-01-03 |    500 |          1200 |        600.0 |
| 2026-01-04 |    300 |          1500 |        500.0 |
| 2026-01-05 |    300 |          1800 |        366.7 |
| 2026-01-08 |    200 |          2000 |        266.7 |
| 2026-01-09 |    300 |          2300 |        266.7 |
| 2026-01-10 |    500 |          2800 |        333.3 |
+------------+--------+---------------+--------------+

Frames

The frame is the subset of the partition that the function looks at:

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW: the current row and the two before it (a 3-row moving window).
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: from the start up to now (a running total).
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: from now to the end.
RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW: by value (dates within the last 7 days), not by row count.

When you give ORDER BY but no frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With RANGE, rows that tie on the sort key are added together. If your running total must grow row by row, use ROWS and a unique ordering.

Running total per rep:

SQL

SELECT rep, sold_on, amount,
       SUM(amount) OVER (PARTITION BY rep ORDER BY sold_on) AS rep_running
FROM sales
WHERE rep IN ('Asha', 'Rohan')
ORDER BY rep, sold_on;

Output

+-------+------------+--------+-------------+
| rep   | sold_on    | amount | rep_running |
+-------+------------+--------+-------------+
| Asha  | 2026-01-03 |    500 |         500 |
| Asha  | 2026-01-08 |    200 |         700 |
| Rohan | 2026-01-04 |    300 |         300 |
| Rohan | 2026-01-10 |    500 |         800 |
+-------+------------+--------+-------------+

Looking at other rows: LAG, LEAD, FIRST_VALUE#

SQL

SELECT rep, sold_on, amount,
       LAG(amount)  OVER w                      AS prev_amount,
       amount - LAG(amount) OVER w              AS change_vs_prev,
       LEAD(sold_on) OVER w                     AS next_sale,
       FIRST_VALUE(amount) OVER w               AS first_amount
FROM sales
WHERE rep IN ('Asha', 'Rohan')
WINDOW w AS (PARTITION BY rep ORDER BY sold_on)
ORDER BY rep, sold_on;

Output

+-------+------------+--------+-------------+----------------+------------+--------------+
| rep   | sold_on    | amount | prev_amount | change_vs_prev | next_sale  | first_amount |
+-------+------------+--------+-------------+----------------+------------+--------------+
| Asha  | 2026-01-03 |    500 |        NULL |           NULL | 2026-01-08 |          500 |
| Asha  | 2026-01-08 |    200 |         500 |           -300 | NULL       |          500 |
| Rohan | 2026-01-04 |    300 |        NULL |           NULL | 2026-01-10 |          300 |
| Rohan | 2026-01-10 |    500 |         300 |            200 | NULL       |          300 |
+-------+------------+--------+-------------+----------------+------------+--------------+

LAG(col, n, default) reads n rows back (default 1). LEAD reads forward. At the edges you get NULL, or the default you supply.
FIRST_VALUE, LAST_VALUE and NTH_VALUE read a position in the frame. LAST_VALUE with the default frame returns the current row, so give it ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
The WINDOW w AS (...) clause names a window definition so you don't repeat it.

Typical uses: month-over-month growth, days between a customer's orders, detecting gaps in sequences, and session analysis.