Window Functions

#Introduction

Window functions are one of the most powerful SQL features in MySQL 8.0+ and one of the most frequently tested topics in SQL interviews. If you understand them well, many difficult SQL problems become much easier.

You'll learn OVER(), PARTITION BY, ORDER BY inside window functions, ROW_NUMBER(), RANK(), DENSE_RANK(), window functions vs GROUP BY, Top-N per group, running totals, enterprise analytics queries and performance considerations.

#What is a Window Function?

That's the biggest difference from GROUP BY:

#GROUP BY Example

SELECT department,
       AVG(salary) AS avg_salary
FROM employee
GROUP BY department;

The individual employee rows disappear — we get one row per department.

#Window Function Example

Show each employee's name, department, salary and their department's average salary:

SELECT name,
       department,
       salary,
       AVG(salary) OVER (PARTITION BY department) AS department_average
FROM employee;

Every employee row is preserved — this is the power of window functions.

#GROUP BY vs Window Functions

#What is OVER()?

OVER() tells SQL that a function should operate as a window function: FUNCTION() OVER (...).

AVG(salary) OVER ()
SUM(amount) OVER ()
ROW_NUMBER() OVER (ORDER BY salary DESC)
RANK()       OVER (ORDER BY salary DESC)
DENSE_RANK() OVER (ORDER BY salary DESC)

Aggregate Without vs With OVER()

SELECT AVG(salary) FROM employee;        -- one row: the company average

SELECT name,
       salary,
       AVG(salary) OVER () AS company_average
FROM employee;

The average is calculated across all employees, but every row remains visible. With no PARTITION BY, the entire result set is one window.

#What is PARTITION BY?

PARTITION BY divides rows into logical groups called partitions — think of it as a virtual GROUP BY that doesn't collapse rows.

#ORDER BY Inside a Window

ORDER BY determines the sequence of rows inside the window:

SELECT name,
       salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employee;

The database logically orders employees from highest to lowest salary, then assigns row numbers.

#ROW_NUMBER()

Assigns a unique sequential number to every row: ROW_NUMBER() OVER (ORDER BY column).

ROW_NUMBER() Never Repeats

Even if Tom and Mary both earned 85000, they'd get different numbers (2 and 3). ROW_NUMBER() never gives the same number to two rows.

Deterministic ROW_NUMBER()

ROW_NUMBER() OVER (ORDER BY salary DESC) doesn't define which tied employee comes first. Add a tie-breaker:

ROW_NUMBER() OVER (ORDER BY salary DESC, employee_id ASC)

Highest salary first; if salaries are equal, lowest employee_id first.

ROW_NUMBER() with PARTITION BY

Rank employees independently inside each department:

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

PARTITION BY resets the numbering for each department.

#RANK()

RANK() lets tied rows share a rank — but skips ranks after a tie. With David 90000, Tom 85000, Mary 85000, John 70000:

SELECT name,
       salary,
       RANK() OVER (ORDER BY salary DESC) AS rank_no
FROM employee;

Rank 3 is skipped. Think of a competition: two people share 2nd place, so the next person is 4th — competition ranking.

#DENSE_RANK()

DENSE_RANK() also gives tied values the same rank, but doesn't skip ranks:

SELECT name,
       salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS rank_no
FROM employee;

#ROW_NUMBER() vs RANK() vs DENSE_RANK()

One of the most common SQL interview questions.

#Highest-Paid Employee Per Department

One of the most common interview problems:

WITH ranked_employees AS (
    SELECT e.*,
           ROW_NUMBER() OVER (
               PARTITION BY department
               ORDER BY salary DESC
           ) AS rn
    FROM employee e
)
SELECT *
FROM ranked_employees
WHERE rn = 1;

ROW_NUMBER() and Ties

If two employees share a department's highest salary, WHERE rn = 1 returns only one of them. To return all tied employees, use RANK() or DENSE_RANK():

WITH ranked_employees AS (
    SELECT e.*,
           DENSE_RANK() OVER (
               PARTITION BY department
               ORDER BY salary DESC
           ) AS salary_rank
    FROM employee e
)
SELECT *
FROM ranked_employees
WHERE salary_rank = 1;

#Top-N Per Group

Top 3 employees per department:

WITH ranked_employees AS (
    SELECT e.*,
           ROW_NUMBER() OVER (
               PARTITION BY department
               ORDER BY salary DESC
           ) AS rn
    FROM employee e
)
SELECT *
FROM ranked_employees
WHERE rn <= 3;

This is Top-N per group — one of the most important window-function patterns. Memorize the template:

WITH ranked_data AS (
    SELECT t.*,
           ROW_NUMBER() OVER (
               PARTITION BY group_column
               ORDER BY ranking_column DESC
           ) AS rn
    FROM table_name t
)
SELECT *
FROM ranked_data
WHERE rn <= N;

Examples: top 3 employees per department, top 5 products per category, top 10 customers per region, latest transaction per account, most recent order per customer.

Second-Highest Salary in Each Department

WITH ranked_employees AS (
    SELECT e.*,
           DENSE_RANK() OVER (
               PARTITION BY department
               ORDER BY salary DESC
           ) AS salary_rank
    FROM employee e
)
SELECT *
FROM ranked_employees
WHERE salary_rank = 2;

Why DENSE_RANK()? The requirement usually means the second-highest distinct salary — and if several employees earn it, all of them should be returned.

Lowest-Paid Employee Per Department

Same pattern with ORDER BY salary ASC and WHERE rn = 1.

#Aggregates Beside Every Row

-- Department average beside every employee
SELECT name, department, salary,
       AVG(salary) OVER (PARTITION BY department) AS department_average
FROM employee;

-- Salary vs department average
SELECT name, department, salary,
       AVG(salary) OVER (PARTITION BY department)          AS department_average,
       salary - AVG(salary) OVER (PARTITION BY department) AS difference_from_average
FROM employee;

-- Department payroll
SELECT name, department, salary,
       SUM(salary) OVER (PARTITION BY department) AS department_payroll
FROM employee;

-- Company average and company payroll (no PARTITION BY = one window)
SELECT name, salary,
       AVG(salary) OVER () AS company_average,
       SUM(salary) OVER () AS company_payroll
FROM employee;

Compare each employee with their department without losing employee-level detail — very useful in payroll analysis, performance reports and compensation analytics.

#Running Totals

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

Row 1 = 1000; row 2 = 1000 + 2000 = 3000; row 3 = 1000 + 2000 + 1500 = 4500 — the window expands from the first row to the current row.

Why Specify ROWS Explicitly?

SUM(amount) OVER (ORDER BY order_date) uses the default frame RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which treats rows with the same order_date as peers — they're added together and all get the same running total. For predictable row-by-row totals, write the ROWS frame explicitly and add a unique tie-breaker:

SUM(amount) OVER (
    ORDER BY order_date, order_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

Running Total Per Customer

SELECT customer_id,
       order_date,
       amount,
       SUM(amount) OVER (
           PARTITION BY customer_id
           ORDER BY order_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS customer_running_total
FROM orders;

The running total resets for each customer.

#Real-World Examples

Top Salesperson Per Region

WITH sales_rank AS (
    SELECT s.*,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY sales DESC) AS rn
    FROM sales s
)
SELECT *
FROM sales_rank
WHERE rn = 1;

Top Five Customers

WITH customer_revenue AS (
    SELECT customer_id, SUM(amount) AS revenue
    FROM orders
    GROUP BY customer_id
),
ranked_customers AS (
    SELECT customer_id,
           revenue,
           RANK() OVER (ORDER BY revenue DESC) AS customer_rank
    FROM customer_revenue
)
SELECT *
FROM ranked_customers
WHERE customer_rank <= 5;

Step 1 aggregates revenue, step 2 ranks customers, step 3 filters the top 5.

Product Leaderboard

WITH product_sales AS (
    SELECT product_id, SUM(quantity) AS total_sold
    FROM sales
    GROUP BY product_id
)
SELECT product_id,
       total_sold,
       DENSE_RANK() OVER (ORDER BY total_sold DESC) AS product_rank
FROM product_sales;

Products with equal sales get the same rank, with no gaps.

Daily Revenue Running Total

If several orders exist on the same day, aggregate daily revenue first:

WITH daily_revenue AS (
    SELECT order_date, SUM(amount) AS daily_revenue
    FROM orders
    GROUP BY order_date
)
SELECT order_date,
       daily_revenue,
       SUM(daily_revenue) OVER (
           ORDER BY order_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_revenue
FROM daily_revenue;

#Filtering Window Function Results

SELECT *,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employee
WHERE rn = 1;          -- ❌ doesn't work

WHERE is logically evaluated before window functions run, so rn doesn't exist yet. Use a CTE (the standard pattern) or a derived table:

WITH ranked_employees AS (
    SELECT e.*,
           ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employee e
)
SELECT * FROM ranked_employees WHERE rn = 1;

SELECT *
FROM (
    SELECT e.*,
           ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employee e
) ranked_employees
WHERE rn = 1;

Both are valid; the CTE is often easier to read.

#Window ORDER BY vs Final ORDER BY

#Performance Considerations

Window functions are often cleaner than correlated subqueries, self joins and deeply nested queries — but they may need sorting, partitioning and temporary work areas. Performance depends on the number of rows and partitions, sort columns, indexes, memory and the execution plan.

Indexing

For ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC), a potentially useful index is:

CREATE INDEX idx_employee_department_salary
    ON employee (department, salary DESC);

An index doesn't guarantee MySQL avoids sorting for every window query — inspect the plan with EXPLAIN / EXPLAIN ANALYZE.

#Mini Project

Using the employee and orders tables, write queries to:

1. Rank all employees by salary.
2. Rank employees independently inside each department.
3. Find the highest-paid employee in every department.
4. Find all employees tied for the highest salary in every department.
5. Find the top three employees in each department.
6. Find the second-highest distinct salary in each department.
7. Display the department average salary beside every employee.
8. Display the department payroll beside every employee.
9. Calculate a running total of daily sales.
10. Find the latest order for every customer.

#Practice Problems

Easy

1. Generate row numbers for all employees.
2. Generate salary rankings.
3. Generate dense salary rankings.
4. Calculate the company average salary.
5. Calculate the department average salary.
6. Calculate department payroll.

Medium

1. Find the top two employees per department.
2. Find the lowest-paid employee per department.
3. Generate a running total.
4. Build a customer revenue leaderboard.
5. Rank products by quantity sold.
6. Find the latest order per customer.
7. Find the second-highest salary per department.

Interview level

1. Explain ROW_NUMBER() vs RANK() vs DENSE_RANK().
2. Find all employees with the second-highest salary in each department.
3. Rewrite a correlated subquery using window functions.
4. Build a sales leaderboard with ties.
5. Compare window functions with GROUP BY.
6. Explain why window functions can't normally be filtered directly in WHERE.
7. Explain the window ORDER BY vs the final query ORDER BY.
8. Solve Top-N per group.
9. Explain deterministic ranking and tie-breakers.

#The Window Function Mental Model

Window functions are heavily used in banking transaction analysis, sales leaderboards, payroll and HR analytics, financial dashboards, inventory reporting, customer segmentation, BI, Spring Boot analytics APIs, fraud detection, latest-record queries, deduplication, Top-N reports, running balances and revenue analysis.

#Key Takeaways

• Window functions calculate without collapsing rows; OVER() defines the window.
• PARTITION BY creates independent groups; ORDER BY inside OVER() sets the logical sequence.
• ROW_NUMBER() — unique numbers; RANK() — ties with gaps; DENSE_RANK() — ties without gaps.
• Top-N per group is one of the most important patterns.
• Filter window results with CTEs or derived tables.
• Running totals need an explicit frame for predictable row-by-row behaviour.
• Window functions simplify many queries that once needed correlated subqueries or self joins.