SQL Window Functions Explained With Examples

A SQL window function does a calculation across a set of related rows without collapsing them into one row. Every original row stays in the result, and the calculation appears as a new column beside it.

If you have ever wished you could use GROUP BY and still see every individual row, window functions are the answer. This guide covers OVER, PARTITION BY, ROW_NUMBER, RANK, LAG and running totals, each with a real output.

The problem GROUP BY cannot solve

Here is a small employees table. Ravi and Meera earn the same salary on purpose, because ties are where window functions get interesting.

Say you want each employee’s salary next to their department’s average. GROUP BY gives you the average but throws away the employees:

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

Seven employees became three department rows. To keep the employees, add OVER (PARTITION BY ...) after the aggregate:

SELECT name, department, salary,
       AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

OVER is what turns AVG into a window function. PARTITION BY department tells it to calculate the average separately for each department. Every row keeps its own data and gains a dept_avg column.

CREATE TABLE employees (name VARCHAR(50), department VARCHAR(30), salary INT);
INSERT INTO employees VALUES
  ('Asha',  'Sales', 55000),
  ('Ravi',  'Sales', 70000),
  ('Meera', 'Sales', 70000),
  ('Kabir', 'IT',    90000),
  ('Neha',  'IT',    80000),
  ('Arjun', 'HR',    45000),
  ('Divya', 'HR',    55000);

CREATE TABLE daily_sales (sale_date DATE, amount INT);
INSERT INTO daily_sales VALUES
  ('2026-01-01', 200), ('2026-01-02', 350), ('2026-01-03', 150), ('2026-01-04', 400);

CREATE TABLE monthly_revenue (sale_month VARCHAR(7), revenue INT);
INSERT INTO monthly_revenue VALUES
  ('2026-01', 10000), ('2026-02', 12500), ('2026-03', 11000), ('2026-04', 15000);

Window function syntax

function_name(column) OVER (
    PARTITION BY column_to_group_by     -- optional
    ORDER BY column_to_sort_by          -- optional, needed for ranking
)

• function_name – an aggregate (SUM, AVG, COUNT) or a ranking/position function (ROW_NUMBER, RANK, LAG).
• OVER – the keyword that makes it a window function.
• PARTITION BY – splits the rows into independent groups (windows). Leave it out and the whole table is one window.
• ORDER BY – sets the order inside each window. Ranking and “previous row” functions need it.

An empty OVER () means “use every row in the table”. This shows each salary next to the company total:

SELECT name, salary, SUM(salary) OVER () AS company_total
FROM employees;

ROW_NUMBER vs RANK vs DENSE_RANK

These three number rows within each partition. They only differ in how they treat ties:

SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
       RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rnk
FROM employees
ORDER BY department, salary DESC;

Look at Sales. Ravi and Meera tie at 70000, and Asha is next at 55000:

Which of the two tied people gets 1 and which gets 2 under ROW_NUMBER is not guaranteed. If you need a predictable answer, add a tiebreaker such as ORDER BY salary DESC, name.

Top earner in each department

“Find the highest-paid person in each department” is one of the most common SQL interview questions. You rank inside each department and keep rank 1. There is one catch: you cannot put a window function directly in WHERE, because WHERE runs before window functions are calculated. Compute the rank in a subquery, then filter outside it:

SELECT name, department, salary
FROM (
  SELECT name, department, salary,
         RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 1;

Sales returns two people because RANK() gives Ravi and Meera the same rank 1. If you want exactly one row per department, use ROW_NUMBER() instead. Which one is right depends on the question, so ask whether ties should all appear.

Running total with SUM() OVER

Adding ORDER BY inside OVER turns SUM into a cumulative total, where each row shows the sum of everything up to and including itself.

SELECT sale_date, amount,
       SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM daily_sales;

200, then 200 + 350 = 550, then 550 + 150 = 700, then 700 + 400 = 1100. One caveat: if two rows share the same ORDER BY value, they are treated as peers and get the same running total, so order by a column that is unique.

LAG and LEAD: compare with the previous row

LAG(column) reads the value from the previous row, and LEAD(column) reads the next one. They are how you do month-over-month change without a self join.

SELECT sale_month, revenue,
       LAG(revenue) OVER (ORDER BY sale_month) AS prev_month,
       revenue - LAG(revenue) OVER (ORDER BY sale_month) AS change
FROM monthly_revenue;

January has no previous month, so LAG returns NULL there, and the change is NULL as well. You can pass a default, for example LAG(revenue, 1, 0), and an offset, such as LAG(revenue, 2) to look two rows back.

GROUP BY vs window functions

Where window functions work

Window functions are supported by MySQL 8.0 and later, PostgreSQL, SQL Server, Oracle and SQLite 3.25 and later. Older versions such as MySQL 5.7 do not have them, which matters if you ever work on a legacy system.

Practice window functions

Ranking, running totals and month-over-month change are everyday analyst work, and they are the clearest sign in an interview that someone has moved past basic SELECT and JOIN. Write your own in our free online SQL compiler, then try the window function questions in the SQL quiz.

Frequently asked questions

What is the difference between RANK and DENSE_RANK?

Both give tied rows the same rank. RANK() then skips the following numbers (1, 1, 3), while DENSE_RANK() does not (1, 1, 2).

What does PARTITION BY do?

It splits the rows into separate groups, and the window function restarts for each group. It is like GROUP BY, except every row stays in the result.

Can I use a window function in WHERE?

No. WHERE runs before window functions are calculated. Put the window function in a subquery or CTE, then filter on its result in the outer query.

Do window functions slow a query down?

They can on large tables, because the database may need to sort the data for each partition. An index on the PARTITION BY and ORDER BY columns can help. For everyday data sizes you will not notice.

Is a window function the same as an aggregate function?

Not quite. SUM, AVG and COUNT are aggregate functions. When you add OVER (...) they run as window functions and stop collapsing rows.

Related reading

• SQL WHERE vs HAVING – how GROUP BY filtering works.
• Subquery vs CTE – the two ways to build a query on top of another, used in the top-earner example.
• SQL Joins Explained – combining tables before you calculate across them.