Window functions

GROUP BY squeezes many rows into one. Window functions calculate across related rows while keeping every row, which is exactly what you need for rankings, running totals and "compared with last month" reports. MySQL 8.0 and later supports them.

Anatomy of a window

function_name(...) OVER (
    PARTITION BY column      -- optional: restart for each group
    ORDER BY column          -- optional: order inside each group
    ROWS BETWEEN ...         -- optional: frame of rows to include
)

Ranking functions

Assume branch_sales(branch, month, revenue) for a chain of sweet shops.

SELECT branch, month, revenue,
       ROW_NUMBER() OVER (PARTITION BY month ORDER BY revenue DESC) AS row_num,
       RANK()       OVER (PARTITION BY month ORDER BY revenue DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY month ORDER BY revenue DESC) AS dense_rnk
FROM branch_sales;

Revenue	ROW_NUMBER	RANK	DENSE_RANK
90,000	1	1	1
75,000	2	2	2
75,000	3	2	2
60,000	4	4	3

You cannot filter on a window function in WHERE because windows are computed after WHERE. Wrap the query in a CTE:

WITH ranked AS (
    SELECT branch, month, revenue,
           ROW_NUMBER() OVER (PARTITION BY month ORDER BY revenue DESC) AS rn
    FROM branch_sales
)
SELECT * FROM ranked WHERE rn <= 3;   -- top 3 branches every month

Aggregate windows and running totals

Any aggregate becomes a window function when followed by OVER.

SELECT branch, month, revenue,
       SUM(revenue) OVER (PARTITION BY branch ORDER BY month) AS running_total,
       AVG(revenue) OVER (PARTITION BY branch ORDER BY month
                          ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3m,
       revenue / SUM(revenue) OVER (PARTITION BY month) * 100 AS pct_of_month
FROM branch_sales;

When ORDER BY is present without an explicit frame, the default frame runs from the first row to the current row (including ties), which produces a running total.

LAG and LEAD

LAG looks at a previous row and LEAD at a following row — ideal for month-on-month growth.

SELECT branch, month, revenue,
       LAG(revenue) OVER (PARTITION BY branch ORDER BY month) AS prev_month,
       ROUND((revenue - LAG(revenue) OVER (PARTITION BY branch ORDER BY month))
             / LAG(revenue) OVER (PARTITION BY branch ORDER BY month) * 100, 1) AS growth_pct
FROM branch_sales;

The first month of each branch has no previous row, so LAG returns NULL (or a default you pass as the third argument).

MySQL also lets you name a window once and reuse it: ... OVER w FROM branch_sales WINDOW w AS (PARTITION BY branch ORDER BY month).

Key takeaways

• PARTITION BY defines groups; ORDER BY inside OVER defines sequence.
• ROW_NUMBER never ties, RANK leaves gaps after ties, DENSE_RANK does not.
• Filter on window results in an outer query or CTE, never directly in WHERE.
• Use ROWS BETWEEN to control moving averages precisely.