How to calculate a running total that resets every year in Snowflake

A running total that resets every year shows, on each row, the total of the year so far. On 15 May it equals year to date, and on the last day of the year it equals the year’s total. In SQL it is a cumulative sum partitioned by year.

A small example

The orders from how to compare YTD with the same period last year:

CREATE TEMP TABLE Sales (OrderDate DATE, Amount NUMBER(10,2));
INSERT INTO Sales VALUES
  ('2025-01-12', 90),  ('2025-03-03', 110), ('2025-05-10', 60),
  ('2025-05-15', 40),  ('2025-05-16', 70),  ('2025-11-20', 300),
  ('2026-01-10', 100), ('2026-03-05', 150), ('2026-04-08', 80),
  ('2026-05-02', 120), ('2026-05-15', 50),  ('2026-05-20', 300);

The query

SELECT OrderDate, Amount,
       SUM(Amount) OVER (PARTITION BY YEAR(OrderDate)
                         ORDER BY OrderDate
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM Sales
ORDER BY OrderDate;

PARTITION BY YEAR(OrderDate) starts the sum again for every year, and ORDER BY with the frame makes it cumulative. 15 May 2026 shows 500, the year to date of that day, and the last row of 2025 shows 670, the total of the year.

Spell out the window frame

With ORDER BY and no frame, SUM uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE treats all rows with the same OrderDate as one step. Add a second order on 15 May and compare the two frames:

INSERT INTO Sales VALUES ('2026-05-15', 30);

SELECT OrderDate, Amount,
       SUM(Amount) OVER (PARTITION BY YEAR(OrderDate)
                         ORDER BY OrderDate) AS DefaultFrame,
       SUM(Amount) OVER (PARTITION BY YEAR(OrderDate)
                         ORDER BY OrderDate
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RowsFrame
FROM Sales
WHERE YEAR(OrderDate) = 2026
ORDER BY OrderDate;

Only the rows from May are shown. The WHERE clause keeps the whole of 2026, so it does not change the totals of the partition.

• DefaultFrame gives both orders of 15 May the same value, 530: the first row already includes the second order.
• RowsFrame goes row by row, but the order of two rows with the same date is not defined. The intermediate value can be 500 in one run and 480 in the next.

The Snowflake documentation recommends writing the frame explicitly. To make the result stable as well, aggregate to one row per date before the running total:

WITH Daily AS (
  SELECT OrderDate, SUM(Amount) AS Amount
  FROM Sales
  GROUP BY OrderDate
)
SELECT OrderDate, Amount,
       SUM(Amount) OVER (PARTITION BY YEAR(OrderDate)
                         ORDER BY OrderDate
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM Daily
ORDER BY OrderDate;

15 May becomes a single row with 80 and a running total of 530. If you need to keep one row per order, add a unique column such as an order id to the ORDER BY, so every row has a defined position.

Other resets

• Every month: PARTITION BY DATE_TRUNC('month', OrderDate).
• Every fiscal year: partition by the start of the fiscal year, for example DATEADD(month, 3, DATE_TRUNC('year', DATEADD(month, -3, OrderDate))) for a year that starts in April. See how to calculate fiscal year to date.
• Per product and year: PARTITION BY Product, YEAR(OrderDate).

A daily or monthly chart of the running total also needs a row for the days or months without orders, otherwise the line jumps straight from one order to the next. A date spine adds those rows.