Running totals and LAG — COMP 378

Cumulative and relative

Add ORDER BY to a windowed SUM and it becomes a running total — each row sums itself and all prior rows in the window:

SELECT ordered_at, id,
       SUM(1) OVER (ORDER BY ordered_at, id) AS orders_so_far
FROM orders;

LAG(col) peeks at the previous row's value (and LEAD the next) — perfect for deltas, e.g. days since the prior order. LAG returns NULL on the first row, since there's nothing before it.

Both columns come from the same window, ORDER BY ordered_at, id. The running count reads every row up to this one; LAG reads exactly one row back — and there is nothing behind the first row, so it is NULL.

ROWS is SUM(price) OVER (ORDER BY genre, id ROWS UNBOUNDED PRECEDING); RANGE is SUM(price) OVER (ORDER BY genre), the default. RANGE treats every row with the same ORDER BY value as one peer group, so all three fiction rows already carry the whole genre.