CSE Roadmap

Core tier · about 3 hours · 5 parts

Part 4 of 5 · 2 min read

Running totals and window frames

Defining exactly which rows a window function looks at, for a cumulative calculation.

Running totals and window frames

Think of a bank statement listing a running balance beside every transaction. Each line's balance is the sum of every transaction up to and including that line. It is never the whole statement's total at once.

In plain words: ORDER BY inside OVER, combined with an aggregate function, computes a running value by default: the sum, count or average of every row from the start of the partition up to the current row. This is called the window frame, and it can be made explicit for more control.

A worked example. A running total of order count per customer, in order of placement date.

The key lines.

SELECT order_id, customer_id, placed_on,
       COUNT(*) OVER (PARTITION BY customer_id ORDER BY placed_on) AS running_count
FROM orders;

Window frame, running total

A window frame is the specific set of rows a window function looks at for each row, often "from the start of the partition up to the current row" by default when ORDER BY is present. A running total is a sum computed over exactly that growing frame.

Example: running_count above grows by one for each successive order of the same customer, in date order.

Remember this

GROUP BY answers "one number per group." A window function answers "one number per row, computed in the context of its group." Reaching for GROUP BY when every original row still needs to appear is the most common reason to reach for a window function instead.

Quick notes

  • A window function computes a value across related rows without collapsing them, unlike GROUP BY.
  • PARTITION BY divides a window into groups; every original row still appears in the result.
  • ROW_NUMBER never ties; RANK skips numbers after a tie; DENSE_RANK does not.
  • ORDER BY inside OVER, with an aggregate, computes a running value by default.
  • A window frame is the exact set of rows a window function looks at for each row.