Running totals, and the frame you did not know you set

Adding ORDER BY to a window changes what it sums

sum(amount) OVER (PARTITION BY customer_id) gives each row its customer’s total. Add an ORDER BY inside the window and the same function becomes a running total:

SELECT customer_id, placed_at, amount,
       sum(amount) OVER (PARTITION BY customer_id ORDER BY placed_at) AS running_total
FROM orders;

Each row now shows the sum of its customer’s orders up to and including itself. The ORDER BY did not just sort; it introduced a frame, a rule about which of the ordered rows the aggregate covers. With no ORDER BY the frame is the whole partition. With an ORDER BY, the default frame is “from the start of the partition up to the current row”, which is what makes it a running total.

The default is RANGE, and RANGE includes ties

The default frame is written out as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and the word RANGE hides a surprise. In RANGE mode, “current row” means “the current row and every row that ties with it in the ORDER BY”. Take four orders on days 1, 2, 2 and 3 with amounts 10, 20, 30 and 40:

• RANGE (the default): running totals 10, 60, 60, 100. Both day-2 rows see each other, so both show 60.
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 10, 30, 60, 100. Each row sees only the rows physically before it.

Which is right depends on the question. “Cumulative revenue by the end of each day” wants RANGE, because both rows on day 2 belong to a day whose total is 60. “A running balance after each transaction” wants ROWS, because a statement lists one line at a time. Most people want ROWS, get RANGE, and only notice on a day with two orders, when the running total appears to jump.

Say the frame explicitly whenever the ORDER BY can tie. If you order by a unique key there are no ties and the two agree, which is another reason to end every window ORDER BY with the primary key.

Running totals that reset

The partition is the reset. PARTITION BY customer_id restarts at zero per customer; PARTITION BY customer_id, date_trunc('year', placed_at) restarts per customer per year, which is how “year-to-date spend” is built.

Cumulative share, and the 80/20 that is usually 80/15

Divide a running total by the partition total and you have a cumulative share, which turns a sorted list into a curve:

WITH c AS (
  SELECT customer_id, revenue,
         sum(revenue) OVER (ORDER BY revenue DESC, customer_id) AS running,
         sum(revenue) OVER ()                                    AS total,
         row_number() OVER (ORDER BY revenue DESC, customer_id)  AS rn,
         count(*)     OVER ()                                    AS n
  FROM customer_revenue
)
SELECT rn::numeric / n AS share_of_customers, running / total AS share_of_revenue
FROM c;

Find the row where share_of_revenue first exceeds 0.8 and read share_of_customers from it. Marketing folklore says 20%; on real retail data the answer is often between 10% and 30%, and knowing your own number is worth more than the folklore. This is also the way to cut customers into groups of equal revenue rather than equal count.

Running counts and running distinct counts

count(*) OVER (ORDER BY placed_at) is a running count and behaves exactly like the running sum. count(DISTINCT customer_id) OVER (ORDER BY placed_at) is not allowed in Postgres and most engines: distinct aggregates do not work as windows, because a running distinct count cannot be computed by adding one row’s contribution to the previous total. The workaround is to number each customer’s first order with row_number() partitioned by customer, keep the rows where it is 1, and take a running count of those. That is the “customers acquired to date” line on every growth chart.

The habit

Whenever a window has an ORDER BY, write the frame out. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is eleven words that remove an entire category of bug, and a reader who does not know the default will thank you. The next two lessons use frames that look backwards a fixed distance rather than to the start, and the same rule applies there.