SQL Window Functions Tutorial: OVER, PARTITION BY and RANK With Examples

Sooner or later a report asks for something plain GROUP BY cannot do: each salesperson’s figure next to their region’s total, the best sale in each region, or the change compared with last month. SQL window functions solve all three in one clean query.

This tutorial assumes that you know SELECT and GROUP BY. If not, read our SQL GROUP BY and HAVING guide first. We ran every query below on SQLite 3.50, and the results are pasted exactly. The same syntax works in PostgreSQL, MySQL 8 and later, and SQL Server.

What window functions are

A window function performs a calculation across a set of rows that are related to the current row. That set is the “window”. The key difference from an aggregate query is what happens to the rows.

The sample table

All the examples use this sales table with four reps, two regions and two months. Dev and Chitra deliberately tie in February, so you can see how ranking handles ties.

CREATE TABLE sales (rep TEXT, region TEXT, month TEXT, amount INT);

INSERT INTO sales VALUES
 ('Asha',   'North', '2026-01', 100), ('Asha',   'North', '2026-02', 150),
 ('Ben',    'North', '2026-01', 200), ('Ben',    'North', '2026-02', 120),
 ('Chitra', 'South', '2026-01', 300), ('Chitra', 'South', '2026-02', 300),
 ('Dev',    'South', '2026-01',  90), ('Dev',    'South', '2026-02', 300);

OVER and PARTITION BY

First, see what GROUP BY gives: one row per region, and the reps are gone.

SELECT region, SUM(amount) FROM sales GROUP BY region;

-- North | 570
-- South | 990

Now add OVER to keep every rep and show the region total beside each one, here for February only.

SELECT rep, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales
WHERE month = '2026-02';

-- Asha   | North | 150 | 270
-- Ben    | North | 120 | 270
-- Chitra | South | 300 | 600
-- Dev    | South | 300 | 600

PARTITION BY region means “calculate separately for each region”. An empty OVER () would treat the whole result as one window. With this column available, working out each rep’s share becomes simple division: amount * 100.0 / region_total.

ROW_NUMBER vs RANK vs DENSE_RANK

These three look alike until two rows tie. We ranked February’s sales using all three at once. The named WINDOW clause avoids repeating the same definition.

SELECT rep, amount,
       ROW_NUMBER() OVER w AS row_num,
       RANK()       OVER w AS rnk,
       DENSE_RANK() OVER w AS dense
FROM sales
WHERE month = '2026-02'
WINDOW w AS (ORDER BY amount DESC);

-- Chitra | 300 | 1 | 1 | 1
-- Dev    | 300 | 2 | 1 | 1
-- Asha   | 150 | 3 | 3 | 2
-- Ben    | 120 | 4 | 4 | 3

Use RANK for sports-style standings, DENSE_RANK for “second highest salary” questions, and ROW_NUMBER when you need exactly one row per position. That second-highest question appears often in our SQL interview questions for testers.

Top row per group

This is the most useful pattern in the whole topic: the best sale in each region. Window functions run after WHERE, so you must rank in a subquery and filter outside it.

SELECT * FROM (
  SELECT rep, region, month, amount,
         ROW_NUMBER() OVER (PARTITION BY region
                            ORDER BY amount DESC, rep) AS rn
  FROM sales
) ranked
WHERE rn = 1;

-- Ben    | North | 2026-01 | 200 | 1
-- Chitra | South | 2026-01 | 300 | 1

South has three sales of 300, so we added rep as a tie-breaker. Without one, the winner could change between runs. The same pattern also removes duplicates: number the rows inside each duplicate group and keep only row 1.

Running totals

Adding ORDER BY inside OVER makes an aggregate cumulative. Here we first total each month, then add up month by month.

SELECT month, total,
       SUM(total) OVER (ORDER BY month) AS running
FROM (SELECT month, SUM(amount) AS total FROM sales GROUP BY month);

-- 2026-01 | 690 | 690
-- 2026-02 | 870 | 1560

This also shows that GROUP BY and window functions work well together: aggregate first, then calculate across the aggregated rows.

LAG and LEAD: compare with other rows

LAG reads a value from an earlier row, and LEAD reads from a later one, with no self-join required.

SELECT rep, month, amount,
       amount - LAG(amount) OVER (PARTITION BY rep ORDER BY month) AS change
FROM sales
ORDER BY rep, month;

-- Asha   | 2026-01 | 100 | NULL
-- Asha   | 2026-02 | 150 | 50
-- Ben    | 2026-01 | 200 | NULL
-- Ben    | 2026-02 | 120 | -80
-- Chitra | 2026-01 | 300 | NULL
-- Chitra | 2026-02 | 300 | 0
-- Dev    | 2026-01 |  90 | NULL
-- Dev    | 2026-02 | 300 | 210

The first row of each rep is NULL because no earlier month exists. You can supply a default with a third argument, such as LAG(amount, 1, 0). An older way to do this was to join the table to itself, which you can compare in SQL joins explained.

Window frames and moving averages

A frame narrows the window to specific rows around the current one, which is how you calculate moving averages.

SELECT month, total,
       AVG(total) OVER (ORDER BY month
                        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS moving_avg
FROM (SELECT month, SUM(amount) AS total FROM sales GROUP BY month);

-- 2026-01 | 690 | 690.0
-- 2026-02 | 870 | 780.0

February’s value is the average of 690 and 870. If you leave out the frame while using ORDER BY, most databases default to everything from the first row up to the current row and its ties. That default is why SUM becomes a running total, and it is why LAST_VALUE so often surprises people.

Percent of total and NTILE

Window functions can sit on top of a GROUP BY. They run after grouping, which is why SUM(SUM(amount)) OVER () is valid: the inner SUM totals each rep, and the outer one adds those totals across the whole result. NTILE(2) then splits the reps into two equal bands, which is handy for quartiles or top and bottom performers.

SELECT rep, SUM(amount) AS total,
       ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (), 1) AS pct,
       NTILE(2) OVER (ORDER BY SUM(amount) DESC) AS band
FROM sales
GROUP BY rep
ORDER BY total DESC;

-- Chitra | 600 | 38.5 | 1
-- Dev    | 390 | 25.0 | 1
-- Ben    | 320 | 20.5 | 2
-- Asha   | 250 | 16.0 | 2

Named windows

When several columns share one window, repeating it is noisy and easy to get wrong. The WINDOW clause lets you define it once and refer to it by name. It is supported in PostgreSQL, MySQL 8 and SQLite, but SQL Server added it only in the 2022 version, so check your database first.

SELECT rep, month, amount,
       SUM(amount) OVER w AS running,
       FIRST_VALUE(amount) OVER w AS first_month
FROM sales
WHERE region = 'North'
WINDOW w AS (PARTITION BY rep ORDER BY month);

-- Asha | 2026-01 | 100 | 100 | 100
-- Asha | 2026-02 | 150 | 250 | 100
-- Ben  | 2026-01 | 200 | 200 | 200
-- Ben  | 2026-02 | 120 | 320 | 200

Common mistakes

• Filtering in WHERE. WHERE ROW_NUMBER() OVER (...) = 1 is an error. Use a subquery or a CTE.
• Forgetting ORDER BY. Ranking functions and LAG have no meaningful answer without an order.
• No tie-breaker. ROW_NUMBER may pick different rows between runs.
• Confusing the two ORDER BY clauses. The one inside OVER drives the calculation. Only the outer one controls the order of the output.
• Ignoring performance. Windows need sorted data. An index on the partition and order columns can help a lot, as explained in SQL indexes explained.

Final thoughts

Learn SQL window functions in this order: SUM OVER PARTITION BY, then the top-row-per-group pattern, then LAG. Those three cover most real reports. Type the sample table into any database and change one clause at a time to see what moves.

Frequently asked questions

Keep learning