Problem Statement
You want a running total that resets each month per customer. What is the correct setup?
Explanation
To reset totals at month boundaries and per customer, include both customer and month in the partition key. The ORDER BY controls the accumulation order within each partition.
A clean month key can be a date truncated to month or a yyyymm integer.
Code Solution
SolutionRead Only
SELECT customer_id, DATE_TRUNC('month', sale_ts) AS mon, sale_ts, amount,
SUM(amount) OVER (
PARTITION BY customer_id, DATE_TRUNC('month', sale_ts)
ORDER BY sale_ts
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS run_month
FROM sales;