Lesson 26 of 26 · see the practice database

Window functions

GROUP BY squeezes each group into one row. You get the total, and you lose the rows. A window function works out a figure across a group of rows and keeps every row. With one, you can show each employee's salary beside the average of their department, number the rows of each group, or keep a running total. Problems that need awkward subqueries become one line.

Window functions are the last big idea in this course, and the one that most separates a comfortable SQL user from a beginner. All the major databases support them: PostgreSQL, SQL Server and Oracle for many years, MySQL from version 8, SQLite from version 3.25.

OVER makes a function a window function

Compare these. First, GROUP BY: one row per department.

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

Now the window version: every employee, with the department's average alongside.

SELECT name, department, salary,
       ROUND(AVG(salary) OVER (PARTITION BY department)) AS dept_average
FROM employees
ORDER BY department, salary DESC;

The word OVER is what changes an aggregate into a window function. Inside its brackets, PARTITION BY department says "work this out separately for each department". A partition is like a group, except that the rows are not merged.

With that, comparing a row with its group is plain arithmetic.

SELECT name, department, salary,
       salary - ROUND(AVG(salary) OVER (PARTITION BY department)) AS above_or_below
FROM employees
ORDER BY department, salary DESC;

An empty OVER: the whole result

Leave the brackets empty and the window is all the rows.

SELECT name, price,
       SUM(price) OVER () AS total_of_all,
       ROUND(100.0 * price / SUM(price) OVER (), 1) AS percent
FROM products
ORDER BY price DESC
LIMIT 5;

In the lesson on subqueries this needed a subquery for the total. Here it does not.

Numbering rows: ROW_NUMBER

ROW_NUMBER() numbers the rows 1, 2, 3 and so on. It needs to know the order to number them in, so an ORDER BY goes inside the OVER.

SELECT ROW_NUMBER() OVER (ORDER BY price DESC) AS position, name, price
FROM products
LIMIT 5;

Add PARTITION BY and the numbering starts again at 1 for each group.

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

Top N in each group

"The two dearest products in each category" is one of the most asked questions in SQL, in work and in interviews. LIMIT cannot do it, because it cuts the whole result, not each group. Number the rows inside each group, then keep the low numbers.

There is one catch: a window function cannot be used in WHERE, because WHERE runs before window functions are worked out. So the numbering goes in a WITH step, and the filter in the main query.

WITH ranked AS (
  SELECT category, name, price,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS position
  FROM products
)
SELECT category, name, price
FROM ranked
WHERE position <= 2
ORDER BY category, price DESC;

The same pattern gives each customer's most recent order.

WITH numbered AS (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders o
)
SELECT c.name, n.id AS order_id, n.order_date, n.status
FROM numbered n
JOIN customers c ON c.id = n.customer_id
WHERE n.rn = 1
ORDER BY c.name;

Keep this pattern. You will use it for the rest of your working life.

Ranking with ties: RANK and DENSE_RANK

ROW_NUMBER gives every row a different number, even when values tie. Two functions respect ties.

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_number,
       RANK()       OVER (ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

Look at the two people on the same salary.

RANK works like a sports table: two people share third place and the next is fifth. DENSE_RANK leaves no gaps.

With DENSE_RANK, the "second highest salary" question has a clean answer that also handles ties and extends to third, fourth or any place.

WITH r AS (
  SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS place
  FROM employees
)
SELECT name, salary FROM r WHERE place = 2;

Running totals

Put an ORDER BY inside the OVER of a SUM, and the sum becomes cumulative: each row shows the total of everything up to and including itself.

WITH totals AS (
  SELECT o.id, o.order_date, SUM(oi.quantity * p.price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN products p     ON oi.product_id = p.id
  WHERE o.status = 'delivered'
  GROUP BY o.id, o.order_date
)
SELECT id, order_date, total,
       SUM(total) OVER (ORDER BY order_date, id) AS running_total
FROM totals
ORDER BY order_date, id;

Add PARTITION BY for a running total that restarts for each customer, each month or each account. This is how a bank statement shows a balance after every line.

A moving average smooths out ups and downs. The extra words say which rows around the current one to include: here, this row and the two before it.

WITH totals AS (
  SELECT o.id, o.order_date, SUM(oi.quantity * p.price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN products p     ON oi.product_id = p.id
  WHERE o.status = 'delivered'
  GROUP BY o.id, o.order_date
)
SELECT id, order_date, total,
       ROUND(AVG(total) OVER (ORDER BY order_date, id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)) AS average_of_last_3
FROM totals
ORDER BY order_date, id
LIMIT 8;

Looking at the row before or after: LAG and LEAD

LAG fetches a value from the previous row. LEAD fetches from the next. They answer "how does this compare with last time?".

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders
  FROM orders
  GROUP BY strftime('%Y-%m', order_date)
)
SELECT month, orders,
       LAG(orders) OVER (ORDER BY month) AS previous_month,
       orders - LAG(orders) OVER (ORDER BY month) AS change
FROM monthly
ORDER BY month;

The first row has no previous row, so LAG gives NULL there.

How many days passed between each customer's orders?

SELECT customer_id, order_date,
       LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_order,
       CAST(julianday(order_date)
            - julianday(LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)) AS INTEGER) AS days_since
FROM orders
WHERE customer_id IN (1, 2)
ORDER BY customer_id, order_date;

The parts of OVER

function() OVER (
  PARTITION BY columns     -- split the rows into groups (optional)
  ORDER BY columns         -- the order inside each group (needed for numbering, running totals, LAG)
  ROWS BETWEEN ... AND ... -- which neighbouring rows to include (optional)
)

GROUP BY or a window function?

They can be combined. A query may group first and then apply window functions to the grouped rows, as the running total above does.

Common mistakes

• Using a window function in WHERE. Put it in a WITH step or a subquery and filter outside.
• Forgetting ORDER BY inside OVER for ROW_NUMBER, RANK or LAG. Without it, the numbering has no defined order.
• Confusing the ORDER BY inside OVER, which controls the calculation, with the ORDER BY at the end of the query, which controls how the result is displayed. You usually want both.
• Expecting ROW_NUMBER to treat ties fairly. Use RANK or DENSE_RANK for that.

Try it yourself

SELECT name, category, price, MAX(price) OVER (PARTITION BY category) AS category_max FROM products ORDER BY id;

SELECT name, stock, RANK() OVER (ORDER BY stock DESC) AS r FROM products ORDER BY r, name;

WITH x AS (SELECT name, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees) SELECT name, department, salary FROM x WHERE rn = 1 ORDER BY department;

Mark this lesson as done

Examples on this page were run with SQLite 3.50.4 on 12 November 2026. The results shown are the real results.

This is an independent learning site. It is not connected with the makers of any database product. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are named only so that you know where a piece of SQL works. The shop, its customers and its staff in the practice database are made up.