Running total that resets each month

Question

Accepted Answer

The reset is achieved entirely by adding DATETRUNC('month', transactiondate) to the partition. Each month becomes a separate window, so the running sum starts again. Two details worth noting: Adding transactionid to the ORDER BY makes the result deterministic when two transactions share a timestamp. Without it, the running total for tied rows is undefined and can change between runs. The explicit frame (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) is the default for an ordered window in most engines, but stating it avoids the RANGE default's surprising behaviour with ties — RANGE includes all peer rows, which produces the same total for every tied row.