SQL Window Functions: ROW_NUMBER, RANK, LAG and Running Totals

SQL Tutorial · Chapter 30 of 32

SQL window functions perform a calculation across a set of rows related to the current row, without collapsing those rows the way GROUP BY does. Each row keeps its own detail and gains an extra column: a rank, a running total, the previous row’s value or a group total. The OVER clause defines the “window” of rows the function looks at.

This chapter is for anyone who already knows SELECT, WHERE and GROUP BY and wants to write the kind of queries analysts use every day: numbering rows, ranking with ties, running totals, percent of group, comparing with the previous row, moving averages and top-N per group. Every example uses the same six-row Orders table and shows the exact output.

SQL Window Function Syntax

function_name(arguments) OVER (
    [PARTITION BY column]   -- restart the calculation for each group
    [ORDER BY column]       -- order rows inside the window
    [frame clause]          -- e.g. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

Common Window Functions

Sample Table

Example 1: ROW_NUMBER

SELECT OrderID, Amount,
       ROW_NUMBER() OVER (ORDER BY Amount DESC) AS RowNum
FROM Orders;

All six rows survive. Compare this with GROUP BY, which would return one row per group.

Example 2: RANK vs DENSE_RANK with Ties

Ranking orders by month creates ties: three orders share January.

SELECT OrderID, SUBSTR(OrderDate, 1, 7) AS Month,
       RANK()       OVER (ORDER BY SUBSTR(OrderDate, 1, 7)) AS Rnk,
       DENSE_RANK() OVER (ORDER BY SUBSTR(OrderDate, 1, 7)) AS DenseRnk
FROM Orders;

RANK skips to 4 after three tied rows; DENSE_RANK continues with 2.

Example 3: Running Total with SUM OVER

SELECT OrderID, OrderDate, Amount,
       SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal
FROM Orders;

With ORDER BY inside OVER, the default frame runs from the first row up to the current row, which is exactly a running total. The last row, 2800, equals the sum of all six orders.

Example 4: PARTITION BY for Group Totals on Every Row

SELECT OrderID, CustomerID, Amount,
       SUM(Amount) OVER (PARTITION BY CustomerID) AS CustomerTotal,
       ROUND(100.0 * Amount / SUM(Amount) OVER (PARTITION BY CustomerID), 1) AS PctOfCustomer
FROM Orders
ORDER BY CustomerID, OrderID;

Customer 1 has two orders totalling 1500, so the laptop is 1200 ÷ 1500 = 80% of that customer’s spend.

Example 5: LAG to Compare with the Previous Row

SELECT OrderID, OrderDate, Amount,
       LAG(Amount) OVER (ORDER BY OrderDate) AS PrevAmount,
       Amount - LAG(Amount) OVER (ORDER BY OrderDate) AS Change
FROM Orders;

LEAD works the same way but looks at the next row. The first row has no predecessor, so LAG returns NULL; write LAG(Amount, 1, 0) to return 0 instead.

Example 6: Top-N per Group

Window functions cannot appear in WHERE, so wrap the query in a subquery or CTE and filter on the result. This returns each customer’s largest order.

WITH ranked AS (
  SELECT OrderID, CustomerID, Amount,
         ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY Amount DESC) AS rn
  FROM Orders
)
SELECT OrderID, CustomerID, Amount
FROM ranked
WHERE rn = 1
ORDER BY CustomerID;

Change rn = 1 to rn <= 3 for each customer’s top three orders. The subqueries chapter explains the wrapper in more detail.

Example 7: Moving Average with a ROWS Frame

A frame clause limits the window to nearby rows. Here each row averages its own Amount with the two previous orders.

SELECT OrderID, OrderDate, Amount,
       ROUND(AVG(Amount) OVER (ORDER BY OrderDate
             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS MovingAvg3
FROM Orders;

The first two rows average fewer than three values because there are not enough earlier rows yet: (1200 + 25) ÷ 2 = 612.50.

Example 8: NTILE to Split Rows into Buckets

SELECT OrderID, Amount,
       NTILE(2) OVER (ORDER BY Amount DESC) AS Half
FROM Orders;

NTILE(2) splits the six orders into a top half and a bottom half. NTILE(4) gives quartiles, a common way to segment customers by spend.

Works in MySQL, SQL Server, PostgreSQL, SQLite

• Window functions are available in MySQL 8.0+, SQL Server 2012+ (for the full set including LAG, LEAD and running totals), PostgreSQL 8.4+ and SQLite 3.25+. MySQL 5.7 and older do not have them.
• Month extraction differs: SQL Server uses FORMAT(OrderDate, 'yyyy-MM'), MySQL DATE_FORMAT, PostgreSQL TO_CHAR; the window syntax itself is identical.
• In SQL Server, AVG of an integer column returns an integer (612 instead of 612.50 in Example 7). Write AVG(Amount * 1.0) to keep the decimals.
• MySQL, PostgreSQL and SQLite support the WINDOW clause to name a window once and reuse it; SQL Server added it in SQL Server 2022.

Common Errors and Fixes

Tips for Using Window Functions

• Choose ROW_NUMBER when you need exactly one row per position, RANK when ties should share a place and leave gaps, and DENSE_RANK when ties should share a place without gaps.
• Add a unique tie-breaker (for example OrderID) to ORDER BY inside OVER so results are repeatable.
• Use the same window in several columns? Name it once with the WINDOW clause where your database supports it.
• Window functions run on the rows left after WHERE, so filter first to keep large queries fast.

Practice Exercise

1. Number each customer’s orders from oldest to newest.
2. Show each order with the average Amount of all orders on the same row.

-- 1
SELECT CustomerID, OrderID, OrderDate,
       ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS OrderSeq
FROM Orders;
-- customer 1: 101 = 1, 103 = 2; all others = 1

-- 2
SELECT OrderID, Amount, AVG(Amount) OVER () AS OverallAvg FROM Orders;
-- OverallAvg = 466.67 on every row

Related Chapters

• SQL COUNT, AVG and SUM – the aggregate functions used with OVER
• SQL TOP / LIMIT – simple top-N without groups
• SQL from Scratch: full course overview

Running totals, rankings and lookups also have spreadsheet equivalents, which the Excel AI Mastery: 100 Formulas course on NextGenTemplates Academy covers with practice files.

Frequently Asked Questions

What is the difference between a window function and GROUP BY?

GROUP BY merges rows into one row per group. A window function keeps every row and adds the aggregate, rank or offset value as a new column.

What does PARTITION BY do?

It splits the rows into groups so the window function restarts for each group, like GROUP BY but without collapsing the rows.

Can I use a window function in a WHERE clause?

No. Window functions are evaluated after WHERE. Put the query in a subquery or CTE and filter on the window column in the outer query.

What is the difference between RANK and DENSE_RANK?

Both give tied rows the same rank. RANK then skips numbers (1, 1, 3), while DENSE_RANK continues without gaps (1, 1, 2).

How do I calculate a running total in SQL?

Use SUM(column) OVER (ORDER BY date_column). Add PARTITION BY to restart the total for each customer, product or other group.

Does MySQL support window functions?

Yes, from MySQL 8.0. Older versions such as MySQL 5.7 do not, and need self-joins or variables to get similar results.

Wrapping Up

Window functions let you rank, total, compare and bucket rows while keeping every row in the result. Start with ROW_NUMBER and SUM OVER on your own tables, then move to LAG and the top-N-per-group pattern, which solve a large share of everyday reporting questions.

Test Yourself with the Free SQL Quiz

📅 Last updated: October 2026

Chapter 30 of 32 · SQL from Scratch: all 32 chapters