Window Functions

What this page covers

This page contains concept notes only. Practice problems are kept in the separate Practice Problems section. The diagrams use plain text inside code blocks.

Concept Notes: Window Functions

Introduction

What is it? A window function does a calculation across a set of related rows, and then puts the result next to each row, without merging the rows together. The set of rows it looks at is called the window.

Compare this with GROUP BY:

Why do we need it? Many real questions need both the detail and the summary in the same row:

• "Show each order and the total revenue of its region."
• "Show each order and its rank inside the region."
• "Show each order and the previous order's amount."
• "Show a running total by date."

With GROUP BY alone, you lose the individual rows. With a self join or a subquery, the query becomes long and slow. Window functions solve these cases in a clean way.

Real-world analogy Think of a school report card. Each student's row shows their own marks, and next to it, the class average, their rank in the class, and their marks in the previous exam. The class average did not remove any student from the list. It was simply written next to every student. That is how window functions work.

Business use case A sales manager wants a report with every order, plus the total for the order's region, the order's rank inside its region, and the change compared with the salesperson's previous order. All of that can be done in one query with window functions.

Dataset Used

One table, sales_orders, is used in almost every example.

CREATE TABLE sales_orders (
  order_id     INT PRIMARY KEY,
  order_date   DATE NOT NULL,
  region       VARCHAR(20) NOT NULL,
  salesperson  VARCHAR(20) NOT NULL,
  amount       DECIMAL(10,2),
  status       VARCHAR(10) NOT NULL
);

INSERT INTO sales_orders (order_id, order_date, region, salesperson, amount, status) VALUES
(101, '2026-01-01', 'North', 'Asha',  500.00, 'PAID'),
(102, '2026-01-02', 'North', 'Asha',  700.00, 'PAID'),
(103, '2026-01-03', 'North', 'Ravi',  700.00, 'PAID'),
(104, '2026-01-04', 'North', 'Ravi',   NULL,  'PAID'),
(105, '2026-01-05', 'South', 'Neha',  300.00, 'PAID'),
(106, '2026-01-06', 'South', 'Neha',  300.00, 'REFUND'),
(107, '2026-01-07', 'South', 'Asha',  900.00, 'PAID'),
(108, '2026-01-08', 'South', 'Asha',  120.00, 'PAID'),
(109, '2026-01-09', 'East',  'Ravi',  400.00, 'PAID'),
(110, '2026-01-10', 'East',  'Ravi',  600.00, 'PAID'),
(111, '2026-01-11', 'East',  'Neha',  600.00, 'PAID'),
(112, '2026-01-12', 'East',  'Neha',  150.00, 'PAID');

Things to notice in this data, because they matter later:

• Order 104 has a NULL amount.
• Several amounts are tied: 700 twice in North, 300 twice in South, 600 twice in East.
• Order 106 has the status REFUND, all others are PAID.
• Three regions (North, South, East) and three salespeople (Asha, Ravi, Neha).

A note on row order: in the outputs below, the rows often appear grouped by region or salesperson. That happens because the database sorts rows to do the calculation. The order of output rows is never guaranteed unless you write an ORDER BY at the end of the query.

Syntax

function_name(expression) OVER (
    [PARTITION BY column1, column2, ...]   -- optional: make separate groups
    [ORDER BY column3 [ASC | DESC], ...]   -- sort rows inside each group
    [frame clause]                         -- optional: which rows to look at
)

The five families of window functions

Mental Model

Imagine each row has a window in the wall next to it. Through that window, the row can see a group of rows (maybe all rows, maybe only its region, maybe only the rows before it). The row does a calculation on what it sees, and writes the answer in its own row.

 GROUP BY  (rows are merged)              OVER()  (rows are kept)

 region | amount                          region | amount | region_total
 -------+-------                          -------+--------+-------------
 North  | 500        -->  North | 1900     North  | 500    | 1900
 North  | 700                             North  | 700    | 1900
 North  | 700                             North  | 700    | 1900
 North  | NULL                            North  | NULL   | 1900

 4 rows become 1 row                      4 rows stay 4 rows

Three controls decide what each row sees:

How SQL Executes It

Window functions are calculated late in the query, after the rows have been joined, filtered, and grouped.

 FROM / JOIN
     |
 WHERE            filters rows (window results do NOT exist yet)
     |
 GROUP BY
     |
 HAVING
     |
 WINDOW FUNCTIONS  calculated here, on the rows that are left
     |
 SELECT / DISTINCT
     |
 ORDER BY          (the final sort of the output)
     |
 LIMIT

Three rules follow from this order:

1. You cannot use a window function in WHERE, GROUP BY, or HAVING. The value does not exist yet at that step. MySQL raises an error.
2. To filter on a window result (for example, "only rank 1"), put the window query in a subquery or CTE, and filter in the outer query.
3. A window function can use the result of GROUP BY. For example, RANK() OVER (ORDER BY SUM(amount) DESC) works, because SUM(amount) already exists when windows are calculated.

 "Keep only rank 1" pattern:

 SELECT *
 FROM (
     SELECT ..., RANK() OVER (...) AS rnk      <- window runs here
     FROM table
 ) AS t
 WHERE rnk = 1;                                <- filter runs here

1. What Is OVER()?

OVER() turns a normal aggregate expression into a window calculation. It tells SQL: "Do this calculation, but do not collapse the rows. Attach the result to each row."

With empty parentheses, OVER () means: the whole result is one single window.

Example 1: Each order plus the total revenue of the whole table

SQL Query

SELECT
    order_id,
    salesperson,
    amount,
    SUM(amount) OVER () AS total_revenue
FROM sales_orders;

Output

Explanation

• SUM(amount) calculates total revenue.
• OVER() keeps all rows and does not collapse them.
• The same total appears on every row.
• NULL amounts are ignored (order 104 adds nothing).

Example 2: Each order plus the average order amount of the whole table

SQL Query

SELECT
    order_id,
    salesperson,
    amount,
    ROUND(AVG(amount) OVER (), 2) AS avg_order_amount
FROM sales_orders;

Output

Explanation

• AVG(amount) calculates the table average: 5270 / 11 = 479.09.
• OVER() attaches it to each row.
• NULL values are ignored, so the average divides by 11 (the non-NULL amounts), not 12.

Why it works Because the original rows are still there, you can put the detail and the summary side by side, and even compare them (for example, amount - AVG(amount) OVER () shows how far each order is from the average).

2. PARTITION BY

PARTITION BY goes inside OVER(). It says: "Do the same window calculation, but separately for each group."

It is like GROUP BY, but:

• It does not reduce the rows.
• It creates separate mini-windows (partitions).
• The result is attached to every row.

 PARTITION BY region

 +------------- North window -------------+
 | 101  500 | 102  700 | 103  700 | 104 NULL |   -> SUM = 1900
 +-----------------------------------------+
 +------------- South window -------------+
 | 105  300 | 106  300 | 107  900 | 108  120 |   -> SUM = 1620
 +-----------------------------------------+
 +------------- East window --------------+
 | 109  400 | 110  600 | 111  600 | 112  150 |   -> SUM = 1750
 +-----------------------------------------+

GROUP BY vs PARTITION BY on the same idea

SELECT region, SUM(amount) AS region_total
FROM sales_orders
GROUP BY region;

Three rows. The individual orders are gone. The window version below keeps all 12 rows.

Example 3: Each order plus the total revenue of its region

SQL Query

SELECT
    order_id,
    region,
    amount,
    SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales_orders;

Output

Explanation

• SUM(amount) calculates the total sales amount.
• OVER (PARTITION BY region) divides the data by region.
• Each row shows the total of its own region.

Example 4: Each order plus the total revenue of its salesperson

SQL Query

SELECT
    order_id,
    salesperson,
    amount,
    SUM(amount) OVER (PARTITION BY salesperson) AS salesperson_total
FROM sales_orders;

Output

Explanation

• PARTITION BY salesperson makes one window per salesperson.
• Every row shows that salesperson's total sales.
• It does not collapse rows (unlike GROUP BY).

Why it works Asha's orders are in both North and South, but the window follows the salesperson, not the region. A window can be built on any column.

Example 5: Each order plus the count of PAID orders in its region

SQL Query

SELECT
    order_id,
    region,
    status,
    COUNT(CASE WHEN status = 'PAID' THEN 1 END)
        OVER (PARTITION BY region) AS paid_orders_per_region
FROM sales_orders;

Output

Explanation

• CASE WHEN status = 'PAID' THEN 1 END returns 1 only for PAID rows, and NULL otherwise.
• COUNT(...) counts only non-NULL values, so it counts only PAID orders.
• OVER (PARTITION BY region) does this separately for each region.
• South has 3 PAID orders because order 106 is a REFUND.

Why it works This is the conditional counting trick from the CASE notes, now running inside a window. Order 104 still counts as PAID even though its amount is NULL, because the test is on status, not on amount.

3. ORDER BY Inside OVER(): Running Totals

ORDER BY inside OVER() defines the sequence of rows inside each partition, so an aggregate can work step by step (cumulatively).

• Without ORDER BY: the calculation uses the full partition at once.
• With ORDER BY: the calculation grows row by row.

 North, ordered by date, SUM(amount) as a running total

 Row 101:  sees [500]                      ->  500
 Row 102:  sees [500, 700]                 -> 1200
 Row 103:  sees [500, 700, 700]            -> 1900
 Row 104:  sees [500, 700, 700, NULL]      -> 1900   (NULL adds nothing)

Example 6: Running revenue in each region by date

SQL Query

SELECT
    order_id,
    region,
    order_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY region
        ORDER BY order_date
    ) AS running_region_revenue
FROM sales_orders;

Output

Explanation

• The data is divided by region.
• Rows are sorted by order_date inside each region.
• SUM becomes a running total.
• NULL values are ignored.
• If two orders share the same date, the running total for them may not behave the way you expect (see the tie behavior below).

Example 7: Running revenue with a tie-breaker (order_date, then order_id)

SQL Query

SELECT
    order_id,
    region,
    order_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY region
        ORDER BY order_date, order_id
    ) AS running_region_revenue
FROM sales_orders;

Output

The output is the same as Example 6 for this data (no dates are repeated):

Explanation

• The data is divided by region.
• Rows are sorted by order_date, and then by order_id.
• order_id acts as a tie-breaker: if two orders have the same date, the order_id decides which comes first.
• This makes the running total stable and predictable, even when dates repeat.

What ties do to a running total (extra)

SQL Query

SELECT
    order_id,
    region,
    amount,
    SUM(amount) OVER (PARTITION BY region ORDER BY amount) AS running_default,
    SUM(amount) OVER (
        PARTITION BY region
        ORDER BY amount
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_rows
FROM sales_orders
WHERE region = 'East'
ORDER BY amount, order_id;

Output

Why the two columns differ

• With the default frame, rows that have the same ORDER BY value are treated as "peers" and are included together. Both 600 rows show 1750.
• With ROWS, the running total moves one physical row at a time: 1150, then 1750. Which of two tied rows comes first is then decided by the database unless you add a tie-breaker.
• Adding a tie-breaker column to ORDER BY (like order_id) makes every row unique, so both methods agree.

4. Window Frames

A frame says which rows, out of the ordered partition, the current row can actually see.

 Partition (ordered):   r1   r2   r3   r4   r5
 Current row:                     ^ r3

 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW     sees r1 r2 r3
 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW             sees    r2 r3
 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING   sees r1 r2 r3 r4 r5

The default frame (when you do not write one)

The frame matters most for aggregates with ORDER BY and for the value functions (FIRST_VALUE, LAST_VALUE, NTH_VALUE). Ranking functions and LAG/LEAD do not use the frame.

Example 8: A moving sum of the current and previous order (extra)

SQL Query

SELECT
    order_id,
    region,
    order_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY region
        ORDER BY order_date
        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
    ) AS last_two_orders_sum
FROM sales_orders;

Output

Explanation

• ROWS BETWEEN 1 PRECEDING AND CURRENT ROW means: the row just before, and the current row.
• The first row of each region has no row before it, so it only sees itself.
• Order 104 (NULL) adds nothing, so its sum is just the previous order's 700.

Why it works Changing only the frame turns a running total into a moving window. The same idea (with AVG) gives a "moving average", which is very common for trends.

5. Ranking Functions

Ranking functions give each row a position based on a defined order. They do not collapse rows.

Every ranking query follows 3 steps:

Step 1: Decide the group, with PARTITION BY (optional)

• Do you want ranking across the whole table?
• Or separately inside each group (like region)?
• If you do not use PARTITION BY, the entire table becomes one group.

Step 2: Decide the sorting, with ORDER BY (logically required for meaningful ranking)

• Ranking depends on order.
• Highest first? Use DESC. Lowest first? Use ASC.
• MySQL permits some ranking functions without ORDER BY, but then all rows are peers or the row numbering is nondeterministic. Use ORDER BY for meaningful, repeatable ranking.

Step 3: Choose the ranking rule

ROW_NUMBER()

ROW_NUMBER() assigns a unique serial number to each row, based on the defined order.

Think of people standing in a line. You count them: 1, 2, 3, 4... Even if two people have the same score, they still get different positions. No ties allowed. Always sequential. Always unique.

When to use it

• Get exactly one top row per group
• Pagination

Example 9: Rank all orders by amount, highest first

SQL Query

SELECT
    order_id,
    region,
    amount,
    ROW_NUMBER() OVER (
        ORDER BY amount DESC
    ) AS rank_by_amount
FROM sales_orders;

Output

Explanation

• No PARTITION BY, so the whole table is one group.
• ORDER BY amount DESC puts the highest revenue first.
• ROW_NUMBER() gives a unique rank.
• If two orders have the same amount, they still get different numbers.
• NULL values appear last in a descending sort in MySQL (MySQL treats NULL as the lowest value). Other databases may differ.

A warning about ties Orders 102 and 103 both have 700. Which one gets 2 and which gets 3 is not guaranteed unless you add a tie-breaker: ORDER BY amount DESC, order_id.

Example 10: Rank orders by amount inside each region

SQL Query

SELECT
    order_id,
    region,
    amount,
    ROW_NUMBER() OVER (
        PARTITION BY region
        ORDER BY amount DESC
    ) AS rank_in_region
FROM sales_orders;

Output

Explanation

• PARTITION BY region divides the table into regions.
• Ranking restarts inside each region.
• ORDER BY amount DESC puts the highest revenue first in each region.
• ROW_NUMBER() assigns unique positions.

RANK()

RANK() assigns a ranking position based on a specified ordering. It differs from ROW_NUMBER() in one key way:

• Rows with equal values receive the same rank.
• When ties occur, the next rank values are skipped.

Example 11: Rank orders by amount inside each region with RANK()

SQL Query

SELECT
    order_id,
    region,
    amount,
    RANK() OVER (
        PARTITION BY region
        ORDER BY amount DESC
    ) AS rank_in_region
FROM sales_orders;

Output

Explanation, region by region

• East: amounts 600, 600, 400, 150 get ranks 1, 1, 3, 4. Both 600 values receive rank 1. Rank 2 is skipped. Ranking resumes at 3.
• North: amounts 700, 700, 500, NULL get ranks 1, 1, 3, 4. NULL values appear last when ordered DESC, so NULL receives the lowest rank.
• South: amounts 900, 300, 300, 120 get ranks 1, 2, 2, 4. Two rows share rank 2. Rank 3 is skipped.

DENSE_RANK()

DENSE_RANK() also gives tied rows the same rank, but it does not skip numbers. The rank goes up only when the ordered value changes.

Consider the values in descending order: 95, 95, 90, 85.

Two rows share rank 1. The next distinct value receives rank 2 (not 3).

Example 12: DENSE_RANK() on the dataset (extra)

SQL Query

SELECT
    order_id,
    region,
    amount,
    DENSE_RANK() OVER (
        PARTITION BY region
        ORDER BY amount DESC
    ) AS dense_rank_in_region
FROM sales_orders;

Output

ROW_NUMBER vs RANK vs DENSE_RANK side by side

All three use PARTITION BY region ORDER BY amount DESC. For ROW_NUMBER, order_id is added as a tie-breaker.

 East amounts:   600   600   400   150

 ROW_NUMBER:      1     2     3     4      always unique
 RANK:            1     1     3     4      ties share, then a GAP (2 is skipped)
 DENSE_RANK:      1     1     2     3      ties share, NO gap

The "top N per group" pattern

Because a window result cannot be used in WHERE, the pattern is: calculate the rank in a subquery, and filter it outside.

Example 13: The single highest order per region (ties kept)

SQL Query

SELECT *
FROM (
    SELECT
        order_id,
        order_date,
        salesperson,
        region,
        amount,
        RANK() OVER (
            PARTITION BY region
            ORDER BY amount DESC
        ) AS rnk
    FROM sales_orders
) AS dummy
WHERE rnk = 1;

Output

Explanation

• PARTITION BY region divides rows into region groups.
• ORDER BY amount DESC sorts orders from highest to lowest inside each region.
• RANK() assigns ranking positions within each region.
• If several rows share the highest amount, they all get rank 1.
• The outer query filters WHERE rnk = 1.
• The final result shows the highest order(s) per region, including ties.

If you want exactly one row per region Use ROW_NUMBER() with a tie-breaker instead of RANK(), and filter WHERE rn = 1:

ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC, order_id) AS rn

This returns orders 110 (East), 102 (North), and 107 (South): one row each.

Example 14: The top 2 ranks per region

SQL Query

SELECT *
FROM (
    SELECT
        order_id,
        order_date,
        salesperson,
        region,
        amount,
        RANK() OVER (
            PARTITION BY region
            ORDER BY amount DESC
        ) AS rnk
    FROM sales_orders
) AS dummy
WHERE rnk <= 2;

Output

Explanation

• Rows are divided by region using PARTITION BY.
• Orders are sorted highest to lowest using ORDER BY amount DESC.
• RANK() assigns ranking positions within each region.
• The outer query filters WHERE rnk <= 2.
• Because RANK() skips numbers after ties, rank 2 may not exist in some regions. In East and North, the tied rows take ranks 1 and 1, the next rank is 3, so nothing has rank 2.
• The result returns the top 2 ranked positions per region (including ties), not always exactly 2 rows.

The same question with DENSE_RANK() (extra)

With DENSE_RANK() and WHERE rnk <= 2, you get the top 2 distinct amounts per region:

Which function for "top N"?

Example 15: Rank salespeople by total revenue inside each region

SQL Query

SELECT 
   region,
   salesperson,
   SUM(amount) AS revenue, 
   RANK() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS rnk
FROM sales_orders
GROUP BY region, salesperson;

Output

Explanation

• GROUP BY region, salesperson calculates the total revenue per salesperson in each region.
• SUM(amount) computes the revenue.
• PARTITION BY region ranks salespeople inside each region.
• ORDER BY SUM(amount) DESC ranks the highest revenue first.
• RANK() assigns ranking positions. If two salespeople have equal revenue, they share the same rank.

Why it works Window functions run after GROUP BY (see "How SQL Executes It"). By the time RANK() is calculated, SUM(amount) already exists for each region-salesperson group, so it can be used inside the window's ORDER BY. This "GROUP BY first, then rank" pattern is one of the most common in reports.

6. Offset Functions: LAG() and LEAD()

Offset functions let you look sideways in an ordered list. They do not aggregate. They simply fetch a value from another row and place it next to the current row.

How they work:

1. First, create an ordered list using ORDER BY (mandatory).
2. Optionally split the data into groups using PARTITION BY.
3. Then apply:
   • LAG() to look backward
   • LEAD() to look forward

 Asha's orders, ordered by order_id:

 order_id    amount     LAG(amount)    LEAD(amount)
 --------    ------     -----------    ------------
   101        500         NULL            700        <- no row before
   102        700          500            900
   107        900          700            120
   108        120          900           NULL        <- no row after

 LAG  looks UP the list (previous row)
 LEAD looks DOWN the list (next row)

LAG()

For each row, LAG() returns the value from a previous row, based on the window's order.

Very useful for:

• Previous purchase amount
• Previous status
• Previous metrics
• Growth calculations
• Change tracking

Syntax

LAG(expression, offset, default_value)
OVER (PARTITION BY ... ORDER BY ...)

Example 16: The previous order amount (whole table)

SQL Query

SELECT
    order_id,
    order_date,
    region,
    salesperson,
    amount,
    LAG(amount) OVER (ORDER BY order_id) AS prev_amount
FROM sales_orders;

Output

Explanation

• Order 101 has no row before it, so prev_amount is NULL.
• Order 105's prev_amount is NULL for a different reason: a row exists before it (order 104), but that row's amount is itself NULL.

Example 17: Offset = 2 and default = 0

SQL Query

SELECT
    order_id,
    order_date,
    region,
    salesperson,
    amount,
    LAG(amount, 2, 0) OVER (ORDER BY order_id) AS prev_amount
FROM sales_orders;

Output

Explanation

• offset = 2 reaches two rows back.
• Orders 101 and 102 have no row two steps back, so they get the default value 0.00.
• Order 106 looks two rows back at order 104, whose amount is NULL, so it returns NULL, not the default.

Example 18: The previous order amount per person in each region

SQL Query

SELECT
    order_id,
    order_date,
    region,
    salesperson,
    amount,
    LAG(amount) OVER (
        PARTITION BY region, salesperson 
        ORDER BY salesperson, order_id
    ) AS prev_sale
FROM sales_orders;

Output

Explanation

• PARTITION BY region, salesperson makes one window for each person in each region, so "previous" never crosses from one person to another.
• The first row of each window has no previous row, so it gets NULL.
• In the source query, ORDER BY salesperson, order_id includes salesperson, but that column has the same value for the whole partition, so it adds nothing. ORDER BY order_id alone gives the same result.

The edge case for LAG(column, offset, default)

• If the required previous row (based on ORDER BY) does not exist, the function returns the specified default value.
• If the previous row exists but the column value in it is NULL, the function returns NULL, not the default value.
• The default applies only for missing rows, not for missing data inside an existing row.

 Row does not exist          -> default value
 Row exists, value is NULL   -> NULL   (the default is NOT used)

LEAD()

For each row, LEAD() returns the value from the next row. It is the mirror image of LAG.

Used for:

• Next order
• Next date
• Predicting future gaps
• Retention analysis

Syntax

LEAD(expression, offset, default_value)
OVER (PARTITION BY ... ORDER BY ...)

Example 19: The next order amount per person in each region

SQL Query

SELECT
    order_id,
    order_date,
    region,
    salesperson,
    amount,
    LEAD(amount) OVER (
        PARTITION BY region, salesperson 
        ORDER BY salesperson, order_id
    ) AS next_sale
FROM sales_orders;

Output

Explanation

• The last row of each window has no next row, so it shows NULL.
• Order 103's next_sale is NULL because the next row (order 104) exists but its amount is NULL.

Example 20: Previous order amount and the change from it, per salesperson

SQL Query

SELECT
    order_id,
    order_date,
    region,
    salesperson,
    amount,
    LAG(amount, 1, 0) OVER (
        PARTITION BY salesperson 
        ORDER BY order_id
    ) AS prev_sale_by_the_salesperson,
    COALESCE(amount, 0) 
    - LAG(amount, 1, 0) OVER (
        PARTITION BY salesperson 
        ORDER BY order_id
    ) AS `change`
FROM sales_orders;

(change is written with backticks because CHANGE is a reserved word in MySQL.)

Output

Explanation

• The window is per salesperson, in order_id order, so it follows Asha, Neha, and Ravi across regions.
• The first order of each person has no previous row, so the default 0 is used: the change is the amount itself.
• COALESCE(amount, 0) turns a NULL current amount into 0 before subtracting.

Why Ravi's order 109 shows NULL The previous row (order 104) exists, but its amount is NULL. LAG returns NULL there, not the default 0. So prev_sale is NULL, and 400 - NULL is NULL too. This is the edge case above in a real result. To avoid it, wrap the LAG in COALESCE(LAG(amount) OVER (...), 0).

Example 21: Find gaps, the days until each salesperson's next order

SQL Query

SELECT
    order_id,
    region,
    salesperson,
    amount,
    order_date AS cur_order_date,
    LEAD(order_date) OVER (
        PARTITION BY salesperson 
        ORDER BY order_id
    ) AS next_order_date_by_the_salesperson,
    DATEDIFF(
        LEAD(order_date) OVER (
            PARTITION BY salesperson 
            ORDER BY order_id
        ), 
        order_date
    ) AS days_till_next
FROM sales_orders;

Output

Explanation

• LEAD(order_date) fetches the date of the same person's next order.
• DATEDIFF(next_date, current_date) turns it into a number of days (from the Dates notes: DATEDIFF(end, start)).
• Each person's last order has no next order, so both columns are NULL.
• A gap of 5 days between two orders is easy to spot. This is the basis of "days since last order" and retention reports.

The edge case for LEAD(column, offset, default)

• If the required next row (based on ORDER BY) does not exist, the function returns the specified default value.
• If the next row exists but the column value in it is NULL, the function returns NULL, not the default value.
• The default applies only when the next row is missing, not when the value inside the next row is NULL.

7. Value Window Functions

These functions return a specific positional value from the window frame.

• FIRST_VALUE(expr)
• LAST_VALUE(expr)
• NTH_VALUE(expr, n)

They do not calculate aggregates. They simply fetch a value from a position inside the frame.

Value window functions depend heavily on three things:

• PARTITION BY
• ORDER BY
• The window frame (section 4). Unlike ranking or LAG/LEAD, these functions are frame-dependent.

FIRST_VALUE()

Returns the value of the expression from the first row of the frame.

FIRST_VALUE(expr) OVER (...)

Important notes

• It returns the first row of the frame, not always the entire partition.
• If that first value is NULL, it returns NULL.
• MySQL does not support IGNORE NULLS.

Example 22: Each region's first order

SQL Query

SELECT
    order_id,
    region,
    FIRST_VALUE(order_id) 
        OVER (PARTITION BY region ORDER BY order_id) AS region_first_order
FROM sales_orders;

Output

Explanation

• PARTITION BY region divides the table into region-wise groups.
• ORDER BY order_id sorts orders inside each region in ascending order.
• FIRST_VALUE(order_id) returns the order_id from the first row of that ordered partition.
• Since ORDER BY is present, the default frame is "unbounded preceding to current row".
• The first row stays inside every frame, so the same first order_id is repeated for all rows in that region.

Useful for cohort tagging and identifying anchor transactions.

LAST_VALUE()

Returns the value from the last row of the frame.

LAST_VALUE(expr) OVER (...)

If you use ORDER BY but do not specify a frame, the default frame becomes "unbounded preceding to current row". So LAST_VALUE() returns the current row's own value, which is usually not what you wanted.

 Asha's orders: 101, 102, 107, 108      LAST_VALUE(order_id)

 DEFAULT frame (start ... current row)
   row 101 sees [101]                  -> 101
   row 102 sees [101, 102]             -> 102
   row 107 sees [101, 102, 107]        -> 107
   row 108 sees [101, 102, 107, 108]   -> 108     always = the current row

 FULL frame (UNBOUNDED PRECEDING ... UNBOUNDED FOLLOWING)
   every row sees [101, 102, 107, 108] -> 108     the true last order

The correct way to get the true last row

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Example 23: First and last order per salesperson

SQL Query

SELECT
    order_id,
    region,
    salesperson,
    FIRST_VALUE(order_id) 
        OVER (PARTITION BY salesperson ORDER BY order_id) AS first_order,
        
    LAST_VALUE(order_id) 
        OVER (
            PARTITION BY salesperson 
            ORDER BY order_id 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS last_order
FROM sales_orders;

Output

Explanation

• PARTITION BY salesperson creates separate groups for each salesperson.
• ORDER BY order_id sorts each salesperson's orders in order.
• FIRST_VALUE(order_id) returns the smallest order_id (the first order).
• LAST_VALUE(order_id) returns the last row of the frame, not automatically the last row of the partition.
• The default frame with ORDER BY ends at the current row, so without a frame, LAST_VALUE would just return the current row's value.
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING expands the frame to the entire partition, so the true last order is returned.

Common use case: lifecycle analysis and performance tracking.

NTH_VALUE()

Returns the value from the N-th row (1-based: the first row is 1) of the current window frame.

NTH_VALUE(expr, n) OVER (...)

• n is 1-based.
• It is frame-dependent.
• If the frame has fewer rows than n, it returns NULL.

Example 24: The second order in each region

SQL Query

SELECT
    order_id,
    region,
    NTH_VALUE(order_id, 2)
        OVER (
            PARTITION BY region
            ORDER BY order_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS second_order_in_region
FROM sales_orders;

Output

Explanation

• PARTITION BY region separates the data into region-wise groups.
• ORDER BY order_id sorts orders inside each region.
• NTH_VALUE(order_id, 2) fetches the second row's order_id.
• The frame is explicitly expanded to the entire partition with UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
• The second order_id is repeated for every row in that region.
• If a region had fewer than 2 rows, the function would return NULL.

Why the explicit frame matters (extra) Without the frame, the default frame ends at the current row. The first row of each region sees only itself (1 row), so NTH_VALUE(order_id, 2) would be NULL there (order 109 would show NULL instead of 110). NTH_VALUE is completely frame-dependent.

8. Distribution Helpers

NTILE(n)

NTILE(n) divides ordered rows into N groups (buckets) as evenly as possible.

Commonly used for:

• Percentile grouping
• Performance banding
• Equal-sized segmentation
• Ranking tiers

Core concept You have rows arranged in a specific order inside a partition. You want to split them into N equal groups. NTILE(n) does exactly that.

1. Rows are first partitioned (if PARTITION BY is used).
2. Then they are ordered (ORDER BY is mandatory).
3. Then SQL distributes the rows into N buckets one after another.
4. Bucket numbers start from 1 and go up to N.

Example 25: Two buckets inside each region

SQL Query

SELECT 
    order_id,
    salesperson,
    order_date,
    region,
    NTILE(2) OVER(
        PARTITION BY region 
        ORDER BY order_id
    ) AS bucket_no
FROM sales_orders;

Output

Explanation

• PARTITION BY region divides the table into separate region groups, and each region is processed on its own.
• ORDER BY order_id sorts rows inside each region.
• NTILE(2) divides each region into 2 buckets.
• Bucket numbering restarts from 1 inside each region.
• Rows are distributed as evenly as possible inside each region.

Example 26: Two buckets across the whole table

SQL Query

SELECT 
    order_id,
    salesperson,
    order_date,
    region,
    NTILE(2) OVER(
        ORDER BY order_id
    ) AS bucket_no
FROM sales_orders;

Output

Explanation

• No PARTITION BY, so the entire table is one group.
• ORDER BY order_id defines the global sequence.
• NTILE(2) divides all rows into two equal halves (6 and 6).
• Bucket numbers go from 1 to 2 across the dataset.

Example 27: More buckets than rows

SQL Query

SELECT 
    order_id,
    salesperson,
    order_date,
    region,
    NTILE(15) OVER(
        ORDER BY order_id
    ) AS bucket_no
FROM sales_orders;

Output

Explanation

• The table has 12 rows, but you asked for NTILE(15).
• Only 12 buckets receive rows. Buckets 13 to 15 stay empty.
• No error is thrown.
• Each row still gets a bucket number between 1 and 12.

Example 28: A number of rows that does not divide evenly

SQL Query

SELECT
    order_id,
    salesperson,
    order_date,
    region,
    NTILE(5) OVER(ORDER BY order_id) AS bucket_no
FROM sales_orders;

Output

Explanation

• SQL distributes rows as evenly as possible.
• If the total rows are not divisible by N, perfect equality is impossible.
• Earlier buckets receive one extra row.
• The difference between bucket sizes never exceeds 1.

 12 rows, NTILE(5):   12 / 5 = 2 remainder 2

 Bucket 1 => 3 rows     (gets an extra row)
 Bucket 2 => 3 rows     (gets an extra row)
 Bucket 3 => 2 rows
 Bucket 4 => 2 rows
 Bucket 5 => 2 rows

PERCENT_RANK()

PERCENT_RANK() tells you how high a row ranks on a scale from 0 to 1 inside its partition. It is a rank-based percentile value.

How it works

• It uses RANK() logic internally.
• Ties share the same rank.
• The output is always between 0 and 1.
• Formula: (Rank - 1) / (TotalRows - 1)

Value interpretation

• Lowest rank => 0
• The maximum reaches 1 only when at least one row has rank equal to the partition size. If the maximum ORDER BY value is tied, its shared rank can be lower, so PERCENT_RANK may stop below 1.
• Middle values => between 0 and 1
• If only 1 row exists => returns 0

Example 29: How high is each order inside its region?

SQL Query

SELECT
    order_id,
    salesperson,
    order_date,
    region,
    PERCENT_RANK() OVER(PARTITION BY region ORDER BY order_id) AS how_high
FROM sales_orders;

Output

Explanation Each region has 4 rows. The ranks are 1, 2, 3, 4, so the values are (1-1)/3 = 0, (2-1)/3 = 0.333, (3-1)/3 = 0.667, (4-1)/3 = 1.

CUME_DIST()

It answers: "What fraction of rows are less than or equal to me?"

CUME_DIST() represents the cumulative distribution.

Formula: (rows with a value <= the current value) / (total rows in the partition)

Value interpretation

• The smallest value => close to 1 / total_rows
• The largest value => always 1
• Ties share the same cumulative value

Example 30: Cumulative distribution of amounts inside each region

SQL Query

SELECT
    order_id,
    salesperson,
    order_date,
    region,
    amount,
    CUME_DIST() OVER(PARTITION BY region ORDER BY amount) AS cume
FROM sales_orders;

Output

Explanation

• In an ascending sort, NULL comes first in MySQL, so order 104 (NULL) is the "smallest" in North and has cume = 0.25.
• In South, the two 300 rows are tied, and 3 of 4 rows are at or below 300, so both show 0.75.
• In East and North, the two tied top values both show 1.

PERCENT_RANK vs CUME_DIST with ties (extra)

East amounts in ascending order: 150, 400, 600, 600.

PERCENT_RANK starts at 0 and looks at how many rows are below you. CUME_DIST ends at 1 and looks at how many rows are at or below you.

Example 31: Orders in the top 20% of amounts inside each region

SQL Query

SELECT *
FROM (
    SELECT
        order_id,
        salesperson,
        order_date,
        region,
        amount,
        CUME_DIST() OVER(PARTITION BY region ORDER BY amount) AS cume
    FROM sales_orders
) dumy
WHERE cume >= 0.80;

Output

Explanation

• CUME_DIST orders the amounts from low to high in each region.
• A row with cume >= 0.80 is in the top 20% of its region.
• The filter must be in the outer query, because window results cannot be used in WHERE.
• Since each region has only 4 rows, only rows with cume = 1 pass (the next lower value is at 0.75).

9. Named Windows

Named windows improve readability and efficiency when you use several window functions. You define the window logic once, and reference it by name as many times as you like.

A window defines the set of rows a window function works on. Normally, the window logic is written directly inside OVER():

RANK() OVER (PARTITION BY region ORDER BY amount DESC)

If the same logic is needed many times, rewriting it is repetitive and harder to maintain. Named windows solve this.

Syntax

WINDOW window_name AS (
    window_definition
)

• window_name is your chosen name.
• window_definition typically includes PARTITION BY and ORDER BY.

Where the WINDOW clause goes The WINDOW clause is part of the SELECT statement. It is written after FROM (and after WHERE, GROUP BY, HAVING if you have them), and before ORDER BY. It is not a separate statement written before the SELECT.

 SELECT ... OVER window_name ...
 FROM ...
 WHERE ...
 GROUP BY ...
 HAVING ...
 WINDOW window_name AS ( ... )     <- here
 ORDER BY ...
 LIMIT ...

Example 32: Without a named window

SQL Query

SELECT
    order_id,
    region,
    salesperson,
    amount,
    RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rnk
FROM sales_orders;

Example 33: With a named window

SQL Query

SELECT
    order_id,
    region,
    salesperson,
    amount,
    RANK() OVER region_window AS rnk
FROM sales_orders
WINDOW region_window AS (
    PARTITION BY region
    ORDER BY amount DESC
);

Now the window logic is defined once and reused by name.

Note on the aliases: the examples use rnk instead of rank, because RANK, DENSE_RANK, ROW_NUMBER, and the other window function names are reserved words in MySQL 8. Using them as plain alias names causes a syntax error.

Example 34: Reusing one named window several times

SQL Query

SELECT
    order_id,
    region,
    salesperson,
    amount,
    RANK() OVER region_window AS rnk,
    DENSE_RANK() OVER region_window AS dense_rnk
FROM sales_orders
WINDOW region_window AS (
    PARTITION BY region
    ORDER BY amount DESC
);

Output

This keeps the query clean and maintainable. If you later change the ordering, you change it in one place.

Building on a named window (extra)

A named window can be the starting point for another definition:

SELECT
    order_id,
    region,
    amount,
    SUM(amount) OVER region_w                          AS region_total,
    SUM(amount) OVER (region_w ORDER BY order_date)    AS region_running_total
FROM sales_orders
WINDOW region_w AS (PARTITION BY region);

region_w only defines the partition. The first OVER uses it as is (whole partition). The second OVER adds an ORDER BY on top of it, so the same partition produces a running total.

10. Real-life Use Case: Quick Commerce

What is quick commerce? Quick commerce is an on-demand delivery service where products such as groceries, milk, butter, and other essentials are delivered to customers within minutes (around 5 to 20 minutes). It works with dark stores: small warehouses placed in specific areas (pin codes). Each dark store stocks popular items based on demand patterns and delivers them promptly.

How window functions help Inventory management is crucial in these dark stores. Window functions help decide which products are popular and in demand.

Inventory analysis

• Identifying best-selling products: sales data for every product is stored. RANK(), ROW_NUMBER(), or DENSE_RANK() can find the top-selling products in a region or category (fruits, vegetables, electronics).
• Top 20 items analysis: a typical query finds the top 20 most ordered items in the last 7 days in given categories.
• Optimizing inventory: the warehouse manager stocks the top-selling items and removes the under-performing ones. Milk or bread, if ordered often, always stay in stock. A rarely ordered brand of cold drink may be removed.

A note on the table: the queries below use a sales table with the columns product_name, region, and quantity. It is a different table from the sales_orders practice table above (the original notes reuse the name sales_orders for it). Here it is called product_sales to avoid confusion.

Best-selling products in each region

SELECT 
    product_name, 
    region,
    SUM(quantity) AS total_sales,
    RANK() OVER (PARTITION BY region ORDER BY SUM(quantity) DESC) AS sales_rank
FROM product_sales
GROUP BY product_name, region;

Least-selling products (same query, ascending order)

SELECT 
    product_name, 
    region,
    SUM(quantity) AS total_sales,
    RANK() OVER (PARTITION BY region ORDER BY SUM(quantity)) AS sales_rank
FROM product_sales
GROUP BY product_name, region;

A small made-up example to see the result

Suppose the total quantities sold are:

The "top N in the last 7 days" version

SELECT *
FROM (
    SELECT
        product_name,
        region,
        SUM(quantity) AS total_sales,
        RANK() OVER (PARTITION BY region ORDER BY SUM(quantity) DESC) AS sales_rank
    FROM product_sales
    WHERE order_date >= CURRENT_DATE - INTERVAL 7 DAY
    GROUP BY product_name, region
) AS ranked
WHERE sales_rank <= 20;

The WHERE inside the subquery limits the data to the last 7 days before grouping and ranking. The outer WHERE keeps the top 20 per region.

How the data helps the business

• Dynamic inventory decisions: analysts and managers decide which products to prioritize in their dark stores. Milk, butter, and bread are always stocked. Less popular products are phased out.
• Seasonal and event-based adjustments: during events like Diwali or New Year, some products surge. Window functions help spot the trends so stock can be adjusted.
• Business insights: by analyzing sales patterns with window functions, a business can:
  • Increase the stock of high-demand items and improve delivery times.
  • Identify low-performing products and tidy the product catalog.
  • Adjust marketing campaigns based on product sales rank, to improve the Return on Ad Spend (ROAS).

Deep Dive

Internal behavior For each window function, the database sorts or groups the rows by the PARTITION BY and ORDER BY columns, then walks through each partition and calculates the result for every row using the frame. Several window functions that share the same definition can be calculated in one pass, which is another reason named windows are useful.

NULL handling

Performance considerations

• Window functions need the rows sorted by partition and order columns. An index on those columns can help.
• Very large partitions use more memory and time.
• Different PARTITION BY / ORDER BY definitions in the same query cause separate sorts. Reusing one definition (a named window) avoids extra work.
• A correlated subquery or a self join that does what a window function does is usually slower.

Database behavior

• Window functions are available in MySQL 8.0 and later, not in MySQL 5.7.
• The names RANK, DENSE_RANK, ROW_NUMBER, LAG, LEAD, NTILE, FIRST_VALUE, LAST_VALUE, NTH_VALUE, PERCENT_RANK, and CUME_DIST are reserved words in MySQL 8. Avoid them as aliases.
• MySQL supports ROWS and RANGE frames. Some other databases also support a GROUPS frame, which MySQL does not.
• DISTINCT cannot be used inside a window aggregate in MySQL (for example, COUNT(DISTINCT x) OVER (...) is not allowed).
• Window functions cannot be used in WHERE, GROUP BY, or HAVING.

Window function or GROUP BY?

Which function do I need?

Real-world Use Cases

• Sales reports: each order with its region total, its share of the region, and its rank inside the region.
• Top-N reports: the top 3 products per category, the best salesperson per region.
• Running totals: cumulative revenue, account balances, year-to-date figures.
• Growth and change tracking: day-over-day or month-over-month change using LAG().
• Gap and retention analysis: days between a customer's orders using LEAD() and DATEDIFF.
• Lifecycle analysis: a customer's first and last purchase using FIRST_VALUE() and LAST_VALUE().
• Segmentation: splitting customers into quartiles or deciles with NTILE().
• Percentile cut-offs: "top 20% of orders" using CUME_DIST().
• Inventory decisions: best- and least-selling items per region (quick commerce).

Review Tips

Interviewers commonly ask:

• "What is the difference between GROUP BY and a window function?" — GROUP BY collapses rows into one per group. A window function keeps every row and attaches the result.
• "What does PARTITION BY do?" — It splits rows into groups for the window calculation, without collapsing them.
• "Difference between ROW_NUMBER, RANK, and DENSE_RANK?" — Unique numbers; ties with gaps; ties without gaps. Use the tie example 1, 1, 3 vs 1, 1, 2.
• "How do you get the top N rows per group?" — Rank in a subquery or CTE, then filter in the outer query.
• "Why can't you use a window function in WHERE?" — Windows are calculated after WHERE, GROUP BY, and HAVING.
• "What does LAG return for the first row?" — NULL, or the default if you give one.
• "Why does LAST_VALUE often return the current row?" — The default frame ends at the current row. Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
• "What is the default frame?" — With ORDER BY: from the start of the partition to the current row (with tied peers). Without ORDER BY: the whole partition.
• "How would you compute a running total?" — SUM(x) OVER (PARTITION BY ... ORDER BY ...), with a tie-breaker column.
• "What is the difference between PERCENT_RANK and CUME_DIST?" — Formula (rank - 1) / (n - 1) vs rows <= me / n.
• "How does NTILE handle uneven splits?" — Earlier buckets get one extra row, and sizes differ by at most 1.
• "What is a named window?" — A window defined once in the WINDOW clause and reused by name.

Common Mistakes to Avoid

Mistake 1: Using a window function in WHERE

Why it happens: It feels natural to filter on a rank.

Wrong:

SELECT * FROM sales_orders
WHERE RANK() OVER (PARTITION BY region ORDER BY amount DESC) = 1;

MySQL raises an error, because window functions cannot be used in WHERE.

Correct: Calculate the rank in a subquery or CTE, and filter in the outer query (Example 13).

Mistake 2: Thinking PARTITION BY collapses rows like GROUP BY

Why it happens: Both split data into groups.

Result: expecting 3 rows (one per region) and getting all 12.

Correct: PARTITION BY keeps every row. Use GROUP BY when you want one row per group.

Mistake 3: Ranking without a proper ORDER BY

Why it happens: PARTITION BY alone looks like enough.

Result: RANK() OVER (PARTITION BY region) has nothing to order by, so every row ties for rank 1.

Correct: Always give ranking functions an ORDER BY.

Mistake 4: A running total without a tie-breaker

Why it happens: ORDER BY order_date looks unique.

Result: if two rows share the same date, they are treated as peers and show the same running total (or the order between them is not fixed).

Correct: Add a unique tie-breaker: ORDER BY order_date, order_id.

Mistake 5: Using LAST_VALUE without a frame

Why it happens: It looks like "the last row of the group".

Wrong:

LAST_VALUE(order_id) OVER (PARTITION BY salesperson ORDER BY order_id)

It returns the current row's own value, because the default frame ends at the current row.

Correct: Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Mistake 6: Using NTH_VALUE without a full frame

Why it happens: It looks like the frame is not needed.

Result: early rows see fewer than n rows, so they get NULL.

Correct: Use the full frame, as in Example 24.

Mistake 7: Expecting the LAG/LEAD default to replace NULL values

Why it happens: The default sounds like a NULL-filler.

Result: the default applies only when the row is missing. A row that exists with a NULL value gives NULL (order 109 in Example 20).

Correct: Wrap with COALESCE(LAG(col) OVER (...), 0) if NULL values should also become 0.

Mistake 8: Using RANK() when you need exactly one row per group

Why it happens: RANK looks like "the top".

Result: tied rows all get rank 1 (two rows per region in East and North).

Correct: Use ROW_NUMBER() with a tie-breaker and filter rn = 1.

Mistake 9: Using ROW_NUMBER() when ties must be kept

Why it happens: It is the function everyone remembers.

Result: one of two tied rows is dropped, arbitrarily.

Correct: Use RANK() or DENSE_RANK().

Mistake 10: Expecting "top 2" with RANK() to always return two rows per group

Why it happens: rnk <= 2 sounds like "2 rows".

Result: East returns two rows (both rank 1) and no rank 2, because RANK skips after ties.

Correct: Decide if you want 2 positions (RANK), 2 distinct values (DENSE_RANK), or exactly 2 rows (ROW_NUMBER).

Mistake 11: Using a window function name as an alias

Why it happens: AS rank reads naturally.

Wrong:

SELECT RANK() OVER (ORDER BY amount DESC) AS rank FROM sales_orders;

RANK is a reserved word in MySQL 8, so this is a syntax error.

Correct: Use another name such as rnk, or wrap the name in backticks.

Mistake 12: Writing the WINDOW clause in the wrong place

Why it happens: The window is defined "before it is used".

Wrong:

WINDOW w AS (PARTITION BY region);
SELECT RANK() OVER w FROM sales_orders;

The WINDOW clause is part of the SELECT, after FROM/WHERE/GROUP BY/HAVING and before ORDER BY.

Correct: SELECT ... OVER w ... FROM ... WINDOW w AS (...).

Mistake 13: Trusting the output order of a window query

Why it happens: On small tests the rows look grouped and sorted.

Result: the order can change between runs.

Correct: Add an ORDER BY at the end of the query.

Mistake 14: Using COUNT(CASE ... ELSE 0 END) for conditional counts

Why it happens: It looks more "complete".

Wrong:

COUNT(CASE WHEN status = 'PAID' THEN 1 ELSE 0 END) OVER (PARTITION BY region)

0 is not NULL, so COUNT counts every row.

Correct: Leave out the ELSE, so non-matching rows give NULL and are skipped (Example 5), or use SUM(CASE WHEN ... THEN 1 ELSE 0 END).

Mistake 15: Forgetting that aggregates in a window ignore NULL

Why it happens: Order 104 looks like a normal order.

Result: AVG(amount) OVER () divides by 11, not 12.

Correct: Decide on purpose whether a NULL should count as 0 (COALESCE(amount, 0)).

Key Takeaways

• A window function calculates across a set of related rows (the window) and attaches the result to every row, without collapsing the rows.
• OVER () with empty parentheses treats the whole result as one window.
• PARTITION BY splits the rows into separate mini-windows. Unlike GROUP BY, it keeps all rows.
• ORDER BY inside OVER() lines the rows up, and makes aggregates cumulative (running totals). Add a unique tie-breaker column.
• The frame decides which rows a row can see. Default: whole partition without ORDER BY, or start-to-current-row (including ties) with ORDER BY.
• ROW_NUMBER() is always unique. RANK() shares ties and skips numbers (1, 1, 3). DENSE_RANK() shares ties and does not skip (1, 1, 2).
• Window results cannot be used in WHERE. Calculate them in a subquery or CTE and filter outside ("top N per group").
• Window functions run after GROUP BY, so RANK() OVER (ORDER BY SUM(x) DESC) works on grouped data.
• LAG() reads a previous row and LEAD() reads a next row. The default value is used only when the row is missing, not when the value is NULL.
• FIRST_VALUE, LAST_VALUE, and NTH_VALUE depend on the frame. For LAST_VALUE and NTH_VALUE, use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
• NTILE(n) splits rows into n nearly equal buckets. Earlier buckets get one extra row when the split is uneven.
• PERCENT_RANK() is (rank - 1) / (rows - 1). CUME_DIST() is rows <= me / rows.
• Named windows (WINDOW name AS (...)) define a window once and reuse it. The clause goes after FROM/WHERE/GROUP BY/HAVING and before ORDER BY.
• In MySQL 8, window function names are reserved words, so do not use them as aliases.
• Window functions are available from MySQL 8.0, not in 5.7.