lesson 37 Window functions · advanced

Running totals

Add values progressively in a defined order.

the idea

SUM as a window function keeps each row and accumulates values over a frame. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means all earlier rows plus this row.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

orders6 columns · 12 rows

idinteger · key	customer_idinteger	product_idinteger	quantityinteger	statustext	ordered_attext
101	1	1	2	paid	2025-01-01
102	2	3	1	paid	2025-01-02
103	1	2	1	paid	2025-01-05
104	3	5	2	pending	2025-01-08
105	4	4	5	paid	2025-02-01

showing 5 of 12 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

SELECT
  id,
  quantity,
  SUM(quantity) OVER( ORDER BY ordered_at,id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_units
FROM orders;

Watch for: Specify a ROWS frame to avoid a default RANGE frame grouping equal sort values.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1
Keep every order in order. A running total still returns each order. The frame adds this row to the rows before it.
step-1.sql
sql›
```
SELECT id, quantity, ordered_at
FROM orders
ORDER BY ordered_at, id;
```
2
Run the reference. SUM as a window function keeps each row and accumulates values over a frame. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means all earlier rows plus this row.
step-2.sql
sql›
```
SELECT
  id,
  quantity,
  SUM(quantity) OVER( ORDER BY ordered_at,id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_units
FROM orders;
```