MySQL Window Functions: ROW_NUMBER, RANK, LEAD and LAG with Real Examples

What Are Window Functions?

A standard aggregate function collapses many rows into one — SUM() gives you a single total. A window function performs a calculation across a related set of rows but keeps every row in the result. The window is defined by an OVER() clause that specifies which rows are included in the calculation for each row.

Window functions were added to MySQL in version 8.0 and they replace dozens of convoluted self-join subqueries with clean, readable SQL.

ROW_NUMBER — Unique Rank Per Row

SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

This assigns each employee a unique sequential number within their department, ordered by salary descending. The employee with the highest salary in each department gets rank 1. Ties are broken arbitrarily.

RANK and DENSE_RANK — Handling Ties

SELECT
    name,
    score,
    RANK()       OVER (ORDER BY score DESC) AS rank_with_gaps,
    DENSE_RANK() OVER (ORDER BY score DESC) AS rank_no_gaps
FROM leaderboard;

RANK() leaves gaps — if two people tie for rank 1, the next person gets rank 3. DENSE_RANK() fills the gap — the next person gets rank 2. Leaderboards usually use DENSE_RANK; sports standings usually use RANK.

LAG and LEAD — Comparing to Adjacent Rows

SELECT
    date,
    revenue,
    LAG(revenue, 1)  OVER (ORDER BY date) AS prev_day_revenue,
    LEAD(revenue, 1) OVER (ORDER BY date) AS next_day_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY date) AS daily_change
FROM daily_revenue;

LAG() looks backward and gives you the value from the previous row. LEAD() looks forward. Perfect for calculating day-over-day changes or detecting when a value drops below the previous period.

Running Totals with SUM() OVER

SELECT
    date,
    amount,
    SUM(amount) OVER (
        ORDER BY date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM transactions;

The frame clause tells MySQL to sum everything from the beginning of the ordered set up to the current row — a running total that accumulates as dates increase, essential for financial reports and cumulative analytics.

Performance Considerations

Window functions require sorting, so ensure your ORDER BY columns inside OVER() are indexed when running against large tables. For most analytical queries on tables up to a few million rows, performance is excellent on MySQL 8.0+.