SQL Window Functions

Calculate rankings, running totals and comparisons with previous rows using OVER, PARTITION BY and ORDER BY.

Lesson 23 of 102Advanced17 min read

GROUP BY is great for summaries, but it squashes many rows into one. Sometimes you want to keep every row and see a calculation across a group alongside it: each order with the customer's total spend next to it, each product with its rank by price within its category, or each day's sales with a running total so far.

Window functions do exactly this. They perform a calculation across a set of rows related to the current row, called its window, without collapsing those rows together. You recognise them by the OVER keyword. They're one of the most powerful features in modern SQL and a favourite topic in data interviews.

Window functions are supported by SQLite 3.25+, MySQL 8+ and PostgreSQL. In this lesson you'll number and rank rows, calculate running totals and per-group totals, compare each row with the one before it, and solve the classic "top N per group" problem.

The OVER clause

Any aggregate function becomes a window function when you add OVER:

SQL
SELECT name, price, AVG(price) OVER () AS overall_avg
FROM products;

Every product keeps its own row, and each row shows the same overall average. Inside the brackets of OVER you describe the window:

  • PARTITION BY column splits the rows into groups, like GROUP BY but without merging them.
  • ORDER BY column sets the order inside each partition, which matters for rankings and running totals.

AVG(price) OVER (PARTITION BY category) therefore shows each product next to the average price of its own category.

Ranking: ROW_NUMBER, RANK and DENSE_RANK

FunctionTies getExample sequence
ROW_NUMBER()Different numbers1, 2, 3, 4
RANK()The same number, with a gap after1, 2, 2, 4
DENSE_RANK()The same number, no gap1, 2, 2, 3

All three need ORDER BY inside OVER to know what to rank by: RANK() OVER (ORDER BY price DESC). Add PARTITION BY category to restart the numbering in each category.

Running totals and moving averages

With ORDER BY inside OVER, aggregate functions become cumulative: each row includes every earlier row in the window.

SQL
SUM(quantity) OVER (ORDER BY order_date) AS running_total

You can control the window precisely with a frame. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW looks at the current row and the two before it, which gives a three-row moving average when used with AVG. Frames are an advanced feature, but running totals alone cover many real reports.

LAG and LEAD

LAG(column) returns the value from the previous row in the window, and LEAD(column) from the next one:

SQL
LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)

That gives each order the date of the same customer's previous order, so you can calculate the gap between purchases. The first row in each partition has no previous row, so LAG returns NULL there, or a default you supply: LAG(amount, 1, 0).

SQL Window Functions Syntax

Window function syntax

function_name(args) OVER (
    PARTITION BY column
    ORDER BY column
    ROWS BETWEEN n PRECEDING AND CURRENT ROW
) AS alias

ROW_NUMBER() OVER (ORDER BY col)
RANK() OVER (PARTITION BY grp ORDER BY col DESC)
SUM(x) OVER (ORDER BY date)
LAG(x) OVER (ORDER BY date)

SQL Window Functions Summary

  • Window functions calculate across related rows without collapsing them; look for OVER.
  • PARTITION BY groups rows; ORDER BY inside OVER orders them.
  • ROW_NUMBER, RANK and DENSE_RANK number rows and handle ties differently.
  • SUM(...) OVER (ORDER BY ...) gives running totals.
  • LAG and LEAD read the previous and next row; filter window results with a CTE.

SQL Window Functions Examples

Each example shows the code, the output it produces and what each part does.

Example 1

Each product compared with its category

SQL
SELECT category,
       name,
       price,
       ROUND(AVG(price) OVER (PARTITION BY category), 2) AS category_avg,
       RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank_in_category
FROM products
ORDER BY category, rank_in_category;
Output
category   | name         | price | category_avg | rank_in_category
-----------+--------------+-------+--------------+-----------------
Bags       | Backpack     | 34.99 | 34.99        | 1
Home       | Desk lamp    | 24.50 | 24.5         | 1
Kitchen    | Water bottle | 12.00 | 10.38        | 1
Kitchen    | Lunch box    | 8.75  | 10.38        | 2
Stationery | Notebook     | 3.50  | 2.35         | 1
Stationery | Pen          | 1.20  | 2.35         | 2
Line-by-line explanation
  1. Line 4The average is worked out per category, but every product keeps its own row.
  2. Line 5The ranking restarts at 1 in each category, most expensive first.
Example 2

A running total of items ordered

SQL
SELECT order_date,
       quantity,
       SUM(quantity) OVER (ORDER BY order_date, id) AS running_total
FROM orders
ORDER BY order_date, id;
Output
order_date | quantity | running_total
-----------+----------+--------------
2024-07-01 | 1        | 1
2024-07-02 | 5        | 6
2024-07-02 | 10       | 16
2024-07-05 | 1        | 17
2024-07-06 | 2        | 19
2024-07-08 | 1        | 20
Line-by-line explanation
  1. Line 3Each row adds its quantity to the total of all earlier rows. Ordering by id as well as date gives the two orders on 2024-07-02 a fixed order.
Example 3

Top seller per category with ROW_NUMBER

SQL
WITH sales AS (
    SELECT p.category, p.name, SUM(o.quantity) AS units
    FROM products p
    JOIN orders o ON o.product_id = p.id
    GROUP BY p.category, p.name
),
ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY units DESC) AS rn
    FROM sales
)
SELECT category, name, units
FROM ranked
WHERE rn = 1
ORDER BY units DESC, category;
Output
category   | name         | units
-----------+--------------+------
Stationery | Pen          | 10
Kitchen    | Water bottle | 3
Bags       | Backpack     | 1
Home       | Desk lamp    | 1
Line-by-line explanation
  1. Lines 1 to 6First, total units sold for each product.
  2. Lines 7 to 10Number the products within each category, best seller first.
  3. Lines 11 to 14Keep only number 1 from each category; categories with the same units are listed alphabetically. Lunch box has no sales, so Kitchen's top seller is the water bottle.

Common SQL Window Functions Mistakes

Using a window function in WHERE

Don't
SELECT name, RANK() OVER (ORDER BY price DESC) AS r
FROM products
WHERE r <= 3;
Do
WITH ranked AS (
    SELECT name, RANK() OVER (ORDER BY price DESC) AS r FROM products
)
SELECT name FROM ranked WHERE r <= 3;

Forgetting ORDER BY in a ranking

Don't
SELECT name, ROW_NUMBER() OVER () AS n FROM products;   -- arbitrary order
Do
SELECT name, ROW_NUMBER() OVER (ORDER BY name) AS n FROM products;

Using GROUP BY when you need every row

Don't
SELECT category, AVG(price) FROM products GROUP BY category;   -- product names are lost
Do
SELECT name, category, AVG(price) OVER (PARTITION BY category) AS category_avg
FROM products;

SQL Window Functions in Real Projects

Window functions in analytics

Analysts use window functions for leaderboards, "best-selling product in each region", month-over-month growth with LAG, running revenue totals and finding each customer's first purchase with ROW_NUMBER. Many questions that once needed complicated self-joins are now a single, readable query, which is why window functions appear in almost every data analyst interview.

SQL Window Functions FAQ

Which databases support window functions?

PostgreSQL, SQL Server and Oracle have supported them for many years, MySQL since version 8.0 and SQLite since version 3.25. On older MySQL versions, ranking and running totals need self joins, subqueries or session variables instead.

What is a window frame?

The set of rows, around the current row, that a function such as SUM or AVG looks at. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW covers the current row and the two before it, which gives a three-row moving average. Without a frame clause, an ordered window runs from the first row up to the current row.

Why does my running total jump when dates are tied?

With ORDER BY order_date and no frame clause, the default frame uses RANGE, which includes every row with the same date as the current one. Two orders on the same day therefore both show the total for the whole day. Add ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, or a unique tie-breaker such as id, for a row-by-row total.

Can I use window functions together with GROUP BY?

Yes. The query groups first, then the window function runs over the grouped rows. For example, SELECT category, SUM(stock), RANK() OVER (ORDER BY SUM(stock) DESC) FROM products GROUP BY category ranks categories by their total stock.

What is the difference between PARTITION BY and GROUP BY?

Both divide rows into groups, but GROUP BY collapses each group into one row, while PARTITION BY keeps every row and adds the group's calculation alongside it. Use PARTITION BY when you need both the detail and a group value, such as each product's price next to its category average.

SQL Window Functions Exercises

Answer each one and check it instantly. If you get stuck, open the hint before the answer.

Fill in the blankseasy

Complete the query so products are numbered by price, highest first.

SELECT name, ROW_NUMBER()  (ORDER BY price DESC) AS n
FROM products;
Fill in the blanksmedium

Complete the window so the ranking restarts for each category.

RANK() OVER ( category ORDER BY price DESC)
Predict the outputmedium

Three rows have scores 90, 80 and 80. What does DENSE_RANK() OVER (ORDER BY score DESC) give?

Multiple choicemedium

Which function returns the value from the previous row?

Multiple choicehard

How is a window function different from GROUP BY?

SQL Window Functions Quiz

5 questions to check what you learned.

  1. Which keyword marks a window function?

    • OVER
    • WINDOW
    • PARTITION
    • RANKED

  2. What does PARTITION BY do inside OVER?

    • Sorts the whole result
    • Deletes duplicate rows
    • Creates a new table
    • Splits rows into groups so the calculation restarts for each group

  3. Scores 50, 40, 40, 30 with RANK() descending give which ranks?

    • 1, 2, 3, 4
    • 1, 2, 2, 3
    • 1, 2, 2, 4
    • 1, 1, 2, 3

  4. How do you calculate a running total of amount by date?

    • SUM(amount) GROUP BY date
    • SUM(amount) OVER (ORDER BY date)
    • RUNNING(amount)
    • TOTAL(amount) BY date

  5. Why can't you filter on a window function's result in WHERE?

    • WHERE only accepts numbers
    • WHERE is evaluated before window functions are calculated
    • Window functions return NULL in WHERE
    • It's allowed only in PostgreSQL

Related SQL topics

15Subqueries & CTEsUse the result of one query inside another with subqueries, IN, EXISTS, derived tables and WITH clauses.05ORDER BY & GROUP BYSort results, return only the top rows, and summarise data with COUNT, SUM, AVG and GROUP BY.19ViewsSave a query as a named view, query it like a table, and use views to simplify reports and limit access.
Esc
↑↓ to navigateEnter to open