? MySQL Window Functions

? Quick Overview

MySQL Window Functions allow performing calculations across a set of rows related to the current row without collapsing results (unlike GROUP BY). They are ideal for ranking, running totals, lead/lag comparisons and more.

? Key Concepts

Window: The set of rows that the function operates on, defined by OVER().
PARTITION BY: Splits rows into groups (partitions) for the window function.
ORDER BY: Defines ordering inside each partition (important for ranking and cumulative functions).
ROWS / RANGE: Frame clause that controls which rows relative to the current row are included.
Window functions do not reduce row count — they add computed columns.

? Syntax / Theory

? View Code Example

-- Window function general syntax
function_name() OVER (
[PARTITION BY column_name]
[ORDER BY column_name]
[ROWS or RANGE clause]
)

Common Functions

ROW_NUMBER() — unique row number within partition.
RANK() / DENSE_RANK() — ranking functions (with or without gaps).
LAG(col, offset) / LEAD(col, offset) — access neighboring row values.
Aggregate functions with OVER() (e.g., SUM(), AVG()) for running totals etc.

? Code Example

? View Code Example

-- Create sample sales table
CREATE TABLE sales (
id INT AUTO_INCREMENT PRIMARY KEY,
salesperson VARCHAR(50),
amount DECIMAL(10,2)
);

-- Insert sample rows
INSERT INTO sales (salesperson, amount) VALUES
('Alice', 500),
('Bob', 700),
('Alice', 300),
('Bob', 400),
('Charlie', 800);

-- ROW_NUMBER() per salesperson ordered by amount descending
SELECT salesperson, amount,
ROW_NUMBER() OVER (PARTITION BY salesperson ORDER BY amount DESC) AS row_num
FROM sales;

-- Running total per salesperson ordered by id
SELECT salesperson, amount,
SUM(amount) OVER (PARTITION BY salesperson ORDER BY id) AS running_total
FROM sales;

-- LAG() and LEAD() to access previous and next sale
SELECT salesperson, amount,
LAG(amount, 1) OVER (PARTITION BY salesperson ORDER BY id) AS prev_sale,
LEAD(amount, 1) OVER (PARTITION BY salesperson ORDER BY id) AS next_sale
FROM sales;

? Interactive Simulator

Click the buttons below to see how different functions change the result.

SELECT * FROM sales;

ID	Salesperson	Amount	Result

? Live Output / Explanation

What to expect

ROW_NUMBER() will assign sequential numbers within each salesperson's partition. SUM() OVER (...) produces a cumulative sum for each partition (running totals). LAG() and LEAD() return previous/next values (useful for comparisons).

Unlike aggregation with GROUP BY, window functions keep every original row and add computed columns alongside them.

✅ Tips & Best Practices

Always include an ORDER BY inside the window if the operation depends on row order (ranking, running totals).
Use ROW_NUMBER() for deduplication and pagination when combined with a partition.
Prefer LAG()/LEAD() over self-joins for adjacent-row calculations — simpler and faster.
Be careful mixing window functions and aggregate GROUP BY — when both are needed, use subqueries.

? Try It Yourself / Practice Tasks

Create a table of employees with name, department, and salary.
Use RANK() and DENSE_RANK() to compute department-wise salary ranks.
Find previous and next salaries per employee using LAG() and LEAD().
Compute a cumulative salary total per department using SUM() OVER (PARTITION BY department ORDER BY id).