Window Functions

Intermediate

Advanced Queries

ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG, FIRST_VALUE, SUM OVER PARTITION — window functions compute a result across a sliding frame without collapsing rows like GROUP BY.

Overview

Window functions (MySQL 8.0+) compute a result for each row based on a **window** — a set of related rows — without collapsing them into groups the way `GROUP BY` does. Every original row is preserved; the window function adds an extra computed column. The `OVER()` clause defines the window: `PARTITION BY` divides rows into groups (like GROUP BY but without collapsing), `ORDER BY` determines row sequence within the partition, and `ROWS/RANGE BETWEEN` specifies a sliding frame. Common functions: **ranking** (`ROW_NUMBER`, `RANK`, `DENSE_RANK`), **navigation** (`LAG`, `LEAD`, `FIRST_VALUE`, `LAST_VALUE`), and **aggregation** (`SUM`, `AVG`, `COUNT` with `OVER()`). Window functions are evaluated after `WHERE` and `GROUP BY` but before `ORDER BY` and `LIMIT`.

Ranking Functions: ROW_NUMBER, RANK, DENSE_RANK

`ROW_NUMBER()` assigns a unique integer to every row within the partition. `RANK()` assigns the same rank to ties but skips the next rank (1, 2, 2, 4). `DENSE_RANK()` gives tied rows the same rank without gaps (1, 2, 2, 3). Classic use case: "Top N per group" — get the 3 most recent orders per customer.

MySQL 8 — ROW_NUMBER, RANK, DENSE_RANK

-- Top 3 orders per customer by amount
SELECT *
FROM (
    SELECT
        customer_id,
        order_id,
        amount,
        ROW_NUMBER() OVER (PARTITION BY customer_id
                           ORDER BY amount DESC)   AS rn
    FROM orders
) ranked
WHERE rn <= 3;

-- RANK vs DENSE_RANK vs ROW_NUMBER on tied scores
SELECT
    name,
    score,
    RANK()        OVER (ORDER BY score DESC) AS rank_w_gaps,
    DENSE_RANK()  OVER (ORDER BY score DESC) AS rank_no_gaps,
    ROW_NUMBER()  OVER (ORDER BY score DESC) AS unique_row_num
FROM leaderboard;
-- score=100: rank=1, dense=1, row=1
-- score=95:  rank=2, dense=2, row=2
-- score=95:  rank=2, dense=2, row=3  ← same score, same rank
-- score=90:  rank=4, dense=3, row=4  ← RANK skips 3

Navigation Functions: LAG, LEAD, FIRST_VALUE

`LAG(col, n)` returns the value of `col` from `n` rows before the current row in the window order. `LEAD(col, n)` returns `n` rows ahead. These are essential for computing differences between adjacent rows (e.g., day-over-day sales delta, session gap detection). `FIRST_VALUE` / `LAST_VALUE` return the first/last value in the window frame.

MySQL 8 — LAG and LEAD navigation

-- Day-over-day sales change
SELECT
    sale_date,
    daily_total,
    LAG(daily_total) OVER (ORDER BY sale_date)         AS prev_day_total,
    daily_total - LAG(daily_total) OVER (ORDER BY sale_date) AS delta,
    ROUND(
        100.0 * (daily_total - LAG(daily_total) OVER (ORDER BY sale_date))
        / LAG(daily_total) OVER (ORDER BY sale_date),
    2)                                                 AS pct_change
FROM daily_sales
ORDER BY sale_date;

-- LEAD — next row
SELECT
    user_id,
    event_time,
    LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time)
        AS next_event_time,
    TIMESTAMPDIFF(SECOND, event_time,
        LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time))
        AS gap_seconds
FROM user_events;

Aggregate Window Functions and Running Totals

Standard aggregate functions (`SUM`, `AVG`, `COUNT`, `MIN`, `MAX`) work as window functions when paired with `OVER()`. Without a frame clause they default to `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` (cumulative). You can specify a sliding frame for moving averages.

MySQL 8 — aggregate window functions and frames

-- Running total (cumulative SUM)
SELECT
    sale_date,
    daily_total,
    SUM(daily_total) OVER (ORDER BY sale_date
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        AS running_total
FROM daily_sales;

-- 7-day moving average
SELECT
    sale_date,
    daily_total,
    ROUND(AVG(daily_total) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW   -- last 7 rows
    ), 2) AS moving_avg_7d
FROM daily_sales;

-- Percentage of partition total (no collapse)
SELECT
    region,
    salesperson,
    sales,
    ROUND(100.0 * sales / SUM(sales) OVER (PARTITION BY region), 1) AS pct_of_region
FROM sales_data;

Key Points to Remember

1Window functions compute per-row results over a window without collapsing rows (unlike GROUP BY)
2PARTITION BY divides rows into groups; ORDER BY orders rows within each partition
3ROW_NUMBER: unique; RANK: gaps on ties; DENSE_RANK: no gaps on ties
4LAG/LEAD access previous/next rows by offset — perfect for delta/trend calculations
5ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW gives a cumulative running total
6Window functions are evaluated after WHERE/GROUP BY but before ORDER BY/LIMIT

Interview Questions

What is the difference between RANK() and DENSE_RANK()?

EasyAmazon

How would you get the top 3 products by sales for each category without a subquery per category?

MediumGoogle

What does ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW mean?

MediumMicrosoft

How would you calculate the day-over-day percentage change in daily revenue?

MediumShopify

What is the difference between GROUP BY aggregation and SUM() OVER() as a window function?

MediumThoughtWorks

Ask Aria about Window Functions

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

UNION & UNION ALL

Common Table Expressions (CTE)