Window functions answer the questions managers ask every week: how are we tracking so far this year, what changed since last month, and who is ahead. This article shows three patterns on one small table, with the results, so you can see exactly what each one does.
The data
Monthly revenue for two regions. Copy this into MySQL 8 (or any database with window functions) and run the queries as you read.
CREATE TABLE monthly_sales (month DATE, region VARCHAR(10), revenue INT);
INSERT INTO monthly_sales VALUES
('2026-01-01','North',12000), ('2026-02-01','North',13500),
('2026-03-01','North',12800), ('2026-04-01','North',15100),
('2026-01-01','South',9800), ('2026-02-01','South',9100),
('2026-03-01','South',10400), ('2026-04-01','South',11000);
GROUP BY collapses rows. A window function keeps them.
With GROUP BY you get one row per region:
SELECT region, SUM(revenue) AS revenue
FROM monthly_sales
GROUP BY region;
| region | revenue |
|---|---|
| North | 53400 |
| South | 40300 |
With a window function you keep every month and add the region total next to it. The OVER (PARTITION BY region) part says which rows to look at; it does not remove any.
SELECT region, month, revenue,
SUM(revenue) OVER (PARTITION BY region) AS region_total
FROM monthly_sales;
Each North row now shows 53,400 and each South row 40,300. That is what makes a window useful: the detail and the summary sit on the same row, so you can compare them.
Pattern 1: running total
SELECT region, month, revenue,
SUM(revenue) OVER (PARTITION BY region ORDER BY month
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM monthly_sales
ORDER BY region, month;
| region | month | revenue | running_total |
|---|---|---|---|
| North | 2026-01-01 | 12000 | 12000 |
| North | 2026-02-01 | 13500 | 25500 |
| North | 2026-03-01 | 12800 | 38300 |
| North | 2026-04-01 | 15100 | 53400 |
| South | 2026-01-01 | 9800 | 9800 |
| South | 2026-02-01 | 9100 | 18900 |
| South | 2026-03-01 | 10400 | 29300 |
| South | 2026-04-01 | 11000 | 40300 |
Why the ROWS BETWEEN line? When you add ORDER BY inside OVER without a frame, the database uses a RANGE frame. If two rows share the same date they get the same running total, which is rarely what a report needs. Writing the frame makes the result predictable.
Pattern 2: change since last month with LAG
SELECT region, month, revenue,
LAG(revenue) OVER (PARTITION BY region ORDER BY month) AS prev_month,
ROUND(100 * (revenue - LAG(revenue) OVER (PARTITION BY region ORDER BY month))
/ LAG(revenue) OVER (PARTITION BY region ORDER BY month), 1) AS pct_change
FROM monthly_sales
ORDER BY region, month;
| region | month | revenue | prev_month | pct_change |
|---|---|---|---|---|
| North | 2026-01-01 | 12000 | NULL | NULL |
| North | 2026-02-01 | 13500 | 12000 | 12.5 |
| North | 2026-03-01 | 12800 | 13500 | -5.2 |
| North | 2026-04-01 | 15100 | 12800 | 18.0 |
| South | 2026-01-01 | 9800 | NULL | NULL |
| South | 2026-02-01 | 9100 | 9800 | -7.1 |
| South | 2026-03-01 | 10400 | 9100 | 14.3 |
| South | 2026-04-01 | 11000 | 10400 | 5.8 |
January has no previous month, so LAG returns NULL and so does the change. Leave it as NULL rather than 0: a 0% change and "no data" mean different things on a dashboard.
The business reading: North dipped in March and recovered strongly in April; South fell in February and has grown since. That sentence, not the query, is what goes in the report.
Three mistakes to avoid
- Filtering a window function in WHERE. SQL calculates
WHEREbefore window functions, soWHERE pct_change < 0fails. Put the query in a CTE and filter outside it. - Forgetting PARTITION BY. Without it, the running total runs across both regions as one series, and LAG compares North's January with South's April.
- Gaps in the months. LAG takes the previous row, not the previous month. If a month is missing, the comparison silently skips it. Join to a calendar table first if gaps are possible.
Practise
Using the same table, write a query that shows each month's share of that region's total revenue, rounded to one decimal place. Hint: divide revenue by SUM(revenue) OVER (PARTITION BY region).


