Window functions in SQL: OVER, ROW_NUMBER, RANK

Problem

The average, but also the rows

You can already get the average price of every category with GROUP BY:

SQLdatabase: shop

SELECT category_id, ROUND(AVG(price), 2) AS category_avg
FROM products
GROUP BY category_id;

But the manager wants a table with all the products, and next to each one the average of its category, to see how far each one is off. GROUP BY has squashed the products: of 25 rows, 7 are left. In the lesson on correlated subqueries you did it with a subquery for every row. There is a shorter way.

The idea

OVER and PARTITION BY

SQLdatabase: shop

SELECT name, category_id, price,
  ROUND(AVG(price) OVER (PARTITION BY category_id), 2) AS category_avg
FROM products;

AVG(price) OVER (PARTITION BY category_id) works out the average over the group of the row but does not squash the rows: all 25 products are here, each with the average of its own category.

OVER (…) says which rows to work over. That group of rows is called a window, and a function with OVER is called a window function. PARTITION BY splits the rows into groups, like GROUP BY, but without merging them.

One difference from the correlated subquery: for the gift card the average is 20 €, not NULL. PARTITION BY puts all the NULLs into one group, as GROUP BY does, while p2.category_id = p.category_id was not true for NULL.

The idea

A place in a ranking

ROW_NUMBER() gives the row's number in the window. With ORDER BY in OVER you say how the rows are ordered:

SQLdatabase: shop

SELECT name, category_id, price,
  ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS place
FROM products;

In every category the numbers start again, from the most expensive product: the most expensive item in “Kitchen” (category 8) is the knife set, with number 1.

The natural next step is WHERE place = 1, to keep only the first ones. But:

SQLdatabase: shop

SELECT name, category_id, price,
  ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS place
FROM products
WHERE place = 1;

Window functions are worked out after WHERE: first the rows are picked, then they are arranged into windows. So the ranking goes in a step, and the filter goes outside.

Try it

The most expensive in every category

For every category show its name (column category), its most expensive product and the price (columns name and price). The field already numbers the products. Put it in WITH and take only the first ones.

SQLdatabase: shop

SELECT name, category_id, price,
  ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS place
FROM products;

Solution

WITH ranked AS (
  SELECT name, category_id, price,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS place
  FROM products
)
SELECT c.name AS category, r.name, r.price
FROM ranked r
JOIN categories c ON c.id = r.category_id
WHERE r.place = 1;

See it

Shared places

Now a ranking by items ordered in “Bread and pastry” (category 3), with the three functions side by side:

SQLdatabase: shop

WITH sold AS (
  SELECT p.name, p.category_id, SUM(oi.quantity) AS sold
  FROM products p
  JOIN order_items oi ON oi.product_id = p.id
  GROUP BY p.id, p.name, p.category_id
)
SELECT name, sold,
  ROW_NUMBER() OVER (ORDER BY sold DESC, name) AS row_number,
  RANK() OVER (ORDER BY sold DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY sold DESC) AS dense_rank
FROM sold
WHERE category_id = 3;

1. The cheese banitsa and the white bread have 16 items each. What place does RANK give them, and what does DENSE_RANK give the butter roll after them?RANK: 1 and 2; DENSE_RANK: 1 and 1RANK: 1 and 1, the roll is 3rd; DENSE_RANK: the roll is 2ndBoth give the roll 3rd place

ROW_NUMBER gives different numbers even when there is a tie. Which of the two is first is decided by the second ordering (here the name). Without it, nothing is promised.
RANK gives tied rows the same place and then skips: 1, 1, 3.
DENSE_RANK gives the same place and does not skip: 1, 1, 2.

For “the top 3 products”, choose on purpose: with ROW_NUMBER exactly three come out, with RANK everyone who shares a place comes out.

The idea

Running total

With ORDER BY in OVER, the window of every row is “from the start up to here”. How much we have sold from 1 September up to every day (without the cancelled orders):

SQLdatabase: shop

WITH daily AS (
  SELECT o.ordered_at AS day, SUM(oi.quantity * oi.unit_price) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.ordered_at >= '2026-09-01' AND o.status <> 'cancelled'
  GROUP BY o.ordered_at
)
SELECT day, ROUND(revenue, 2) AS revenue,
  ROUND(SUM(revenue) OVER (ORDER BY day), 2) AS running_total
FROM daily
ORDER BY day;

running_total grows row by row: on 3 October it reaches 415.30 €, and on 19 October it is 480.30 €. This is called a running total.

More precisely, if you are curious

If two rows have the same value in ORDER BY, by default both get the sum including the other one. Here every day is one row, so it cannot happen. If you order orders by date and one day has two orders, add a second ordering, such as ORDER BY ordered_at, id, or say ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly.

Try it

How many customers we had

For every customer show the name, the sign-up date, and how many customers the shop had on that date, including them (column customers_so_far).

SQLdatabase: shop

SELECT name, joined_at FROM customers
ORDER BY joined_at;

Solution

SELECT name, joined_at,
  COUNT(*) OVER (ORDER BY joined_at) AS customers_so_far
FROM customers;

The idea

LAG: the previous row

LAG(column) gives the value from the previous row in the window. How many days pass between Maria's orders?

SQLdatabase: shop

SELECT id, ordered_at,
  LAG(ordered_at) OVER (ORDER BY ordered_at) AS previous,
  julianday(ordered_at) - julianday(LAG(ordered_at) OVER (ORDER BY ordered_at)) AS days
FROM orders
WHERE customer_id = 1;

previous is the date of her previous order. julianday turns a date into a day number, so the difference is the number of days between the two orders. The first order has no previous one and gets NULL. The opposite function is LEAD: the next row.

Exercise

Two tasks

1. The shop window on the website shows two products from every category, the most expensive ones.

For every category show its name (column category) and its two most expensive products with their prices (columns name and price).

SQLdatabase: shop

Solution

WITH ranked AS (
  SELECT name, category_id, price,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS place
  FROM products
)
SELECT c.name AS category, r.name, r.price
FROM ranked r
JOIN categories c ON c.id = r.category_id
WHERE r.place <= 2;

2. Marketing wants to know the longest time each customer has waited between two of their orders.

For every customer with at least two orders show the name and the longest pause in days between two of their consecutive orders (column longest_pause).

SQLdatabase: shop

Solution

WITH gaps AS (
  SELECT customer_id,
    julianday(ordered_at) - julianday(LAG(ordered_at) OVER (PARTITION BY customer_id ORDER BY ordered_at)) AS days
  FROM orders
)
SELECT c.name, MAX(g.days) AS longest_pause
FROM gaps g
JOIN customers c ON c.id = g.customer_id
WHERE g.days IS NOT NULL
GROUP BY c.id, c.name;

Test

Check yourself

1. What is the difference between GROUP BY and OVER (PARTITION BY …)?There is noneGROUP BY squashes each group into one row, while OVER keeps all the rows and adds the result to eachOVER works only with ORDER BY

2. Why does WHERE place = 1 give an error if place is ROW_NUMBER() OVER (…)?Because window functions are worked out after WHEREBecause ROW_NUMBER starts from 0Because place is a reserved word

3. What does SUM(revenue) OVER (ORDER BY day) give?The sum from the first day to the current one: a running totalThe grand total on every rowThe sum for the current day only

4. What does LAG(ordered_at) return on the first row?The date of the same rowNULL, because there is no previous rowAn error

Window functions

Cookies