Window functions

A teacher wants a list for every course: each student's score, and next to it the course average and the student's place in the course. GROUP BY can't do this — it collapses each group into a single row, and the names are lost. Window functions keep every row where it is and add a value calculated from neighbouring rows. Rankings, running totals, “change since last month” — a big part of analytics is written with this tool.

OVER: a “window” of rows

As soon as you write OVER after an ordinary aggregate, it becomes a window function. Below, every enrollment shows the average of its own course: no rows are collapsed, yet the average is calculated separately for each course.

SELECT e.course_id, e.student_id, e.score,
       ROUND(AVG(e.score) OVER (PARTITION BY e.course_id), 1) AS course_avg
FROM enrollments AS e
WHERE e.course_id IN (1, 3)
ORDER BY e.course_id, e.score DESC;

course_id | student_id | score | course_avg
1 | 11 | 97 | 87.3
1 | 1 | 92 | 87.3
1 | 5 | 85 | 87.3
1 | 2 | 75 | 87.3
3 | 11 | 94 | 80
3 | 2 | 81 | 80
3 | 6 | 77 | 80
3 | 4 | 68 | 80

ROW_NUMBER, RANK and DENSE_RANK

The three ranking functions differ only when values are tied. Let's rank students by age: three of them are 17 and three are 16. ROW_NUMBER ignores ties and gives 1, 2, 3, so we add a unique second sort column (id) to keep the result stable.

SELECT first_name, age,
       ROW_NUMBER() OVER (ORDER BY age DESC, id) AS row_num,
       RANK()       OVER (ORDER BY age DESC)     AS rnk,
       DENSE_RANK() OVER (ORDER BY age DESC)     AS dense
FROM students
ORDER BY age DESC, id
LIMIT 7;

first_name | age | row_num | rnk | dense
Elvin | 17 | 1 | 1 | 1
Tural | 17 | 2 | 1 | 1
Fidan | 17 | 3 | 1 | 1
Murad | 16 | 4 | 4 | 2
Rəşad | 16 | 5 | 4 | 2
Səbinə | 16 | 6 | 4 | 2
Aysel | 15 | 7 | 7 | 3

PARTITION BY: a separate ranking per group

PARTITION BY course_id turns every course into its own window, and numbering starts from 1 in each course. This solves the question “who is the best student in each course”: first we calculate the places in a CTE, then we keep the rows where pos = 1.

WITH ranked AS (
  SELECT c.title, s.first_name, e.score,
         RANK() OVER (PARTITION BY e.course_id ORDER BY e.score DESC) AS pos
  FROM enrollments AS e
  JOIN students AS s ON s.id = e.student_id
  JOIN courses  AS c ON c.id = e.course_id
)
SELECT title, first_name, score
FROM ranked
WHERE pos = 1
ORDER BY title;

title | first_name | score
Algebra | Fidan | 97
English B1 | Leyla | 90
Geometry | Leyla | 95
Mechanics | Fidan | 94
Organic Chemistry | Elvin | 72
Python Basics | Rəşad | 99
World History | Nigar | 93

Running totals: SUM() OVER (ORDER BY ...)

When you put ORDER BY inside OVER, the window becomes “from the start up to this row”. That is why SUM gives a running total: each month's revenue is added to the sum of the previous months. Below we first calculate monthly revenue in a CTE and then apply a window function to it.

WITH monthly AS (
  SELECT SUBSTR(o.order_date, 1, 7) AS month,
         ROUND(SUM(o.quantity * p.price), 2) AS revenue
  FROM orders AS o
  JOIN products AS p ON p.id = o.product_id
  GROUP BY month
)
SELECT month, revenue,
       ROUND(SUM(revenue) OVER (ORDER BY month), 2) AS running_total
FROM monthly
ORDER BY month;

month | revenue | running_total
2025-01 | 1691 | 1691
2025-02 | 963.99 | 2654.99
2025-03 | 124.98 | 2779.97
2025-04 | 935.99 | 3715.96
2025-05 | 42 | 3757.96
2025-06 | 152.1 | 3910.06
2025-07 | 32 | 3942.06

There is a subtlety: with ORDER BY the default frame is in RANGE mode, which takes rows with the same sort key (peers) together. If two orders share a date, both show the full total for that day. For a row-by-row running total, add a unique key or write the frame explicitly: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

SELECT id, order_date, quantity,
       SUM(quantity) OVER (ORDER BY order_date)     AS by_range,
       SUM(quantity) OVER (ORDER BY order_date, id) AS by_row
FROM orders
WHERE order_date < '2025-05-01'
ORDER BY order_date, id;

id | order_date | quantity | by_range | by_row
1 | 2025-01-15 | 1 | 3 | 1
2 | 2025-01-15 | 2 | 3 | 3
3 | 2025-02-02 | 20 | 23 | 23
4 | 2025-02-10 | 1 | 24 | 24
5 | 2025-03-05 | 1 | 25 | 25
6 | 2025-03-18 | 2 | 27 | 27
7 | 2025-04-09 | 1 | 31 | 28
8 | 2025-04-09 | 3 | 31 | 31

LAG and LEAD: comparing with a neighbouring row

LAG(x) returns the value from the previous row in the window, and LEAD(x) from the next row. With them, “change since last month” is a single query. The first month has no predecessor, so LAG gives NULL there. LAG(x, 2) looks two rows back, and LAG(x, 1, 0) returns 0 instead of NULL.

WITH monthly AS (
  SELECT SUBSTR(o.order_date, 1, 7) AS month,
         ROUND(SUM(o.quantity * p.price), 2) AS revenue
  FROM orders AS o
  JOIN products AS p ON p.id = o.product_id
  GROUP BY month
)
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
       ROUND(revenue - LAG(revenue) OVER (ORDER BY month), 2) AS change
FROM monthly
ORDER BY month;

month | revenue | prev_revenue | change
2025-01 | 1691 | NULL | NULL
2025-02 | 963.99 | 1691 | -727.01
2025-03 | 124.98 | 963.99 | -839.01
2025-04 | 935.99 | 124.98 | 811.01
2025-05 | 42 | 935.99 | -893.99
2025-06 | 152.1 | 42 | 110.1
2025-07 | 32 | 152.1 | -120.1

SELECT category, name, price
       -- add price_rank here
FROM products
ORDER BY category, price DESC;

category | name | price | price_rank
Accessories | Backpack | 55 | 1
Accessories | Water bottle | 12 | 2
Electronics | Laptop | 1450 | 1
Electronics | Smartphone | 899.99 | 2
Electronics | Monitor | 310 | 3
Electronics | Headphones | 120.5 | 4
Games | Chess set | 42 | 1
Home | Desk lamp | 34.99 | 1
Stationery | Pen set | 7.9 | 1
Stationery | Notebook | 3.2 | 2

SELECT customer_id, order_date, quantity
       -- order_no and running_qty
FROM orders
WHERE customer_id IN (1, 2, 3)
ORDER BY customer_id, order_date, id;

customer_id | order_date | quantity | order_no | running_qty
1 | 2025-01-15 | 1 | 1 | 1
1 | 2025-01-15 | 2 | 2 | 3
1 | 2025-07-07 | 10 | 3 | 13
2 | 2025-02-02 | 20 | 1 | 20
2 | 2025-06-30 | 1 | 2 | 21
3 | 2025-02-10 | 1 | 1 | 1
3 | 2025-05-21 | 1 | 2 | 2