Running totals & moving averages

The problem

You want each row to also show something computed over the rows around it: the total so far, the average of the last 3, its share of the whole.

Running total

A windowed SUM with ORDER BY accumulates as it goes — and keeps every row (unlike GROUP BY, which collapses them).

Loading editor…

Moving average (rolling window)

Add an explicit frame to average only the current row and the two before it:

Loading editor…

Without ROWS BETWEEN …, an ORDER BY window defaults to "everything from the start through the current row" — that's the running total, not a rolling window.

Percent of total

Divide the row by an un-ordered window (the whole set):

Loading editor…

SUM(sales) OVER () — empty OVER — is the grand total on every row. Add PARTITION BY for a per-group total.

Month-over-month change

Loading editor…

The first row has no previous row, so LAG is NULL and the change is NULL — usually what you want.

Gotchas

• ROWS vs RANGE. ROWS counts physical rows; RANGE groups peers with equal ORDER BY values. For a plain N-row window you almost always want ROWS.
• Ties in the order. If two rows share the ORDER BY value, a ROWS frame still splits them arbitrarily. Add a tiebreaker for reproducibility.
• Integer division. sales / SUM(...) can floor to 0 — multiply by 100.0 (or cast) first.