Running totals

SUM works as a window function too. With ORDER BY inside OVER, it adds up every row so far, which gives a running total:

WITH daily AS (
  SELECT order_date, COUNT(*) AS orders
  FROM orders
  WHERE order_date >= '2026-10-01'
  GROUP BY order_date
)
SELECT order_date, orders,
       SUM(orders) OVER (ORDER BY order_date) AS running_total
FROM daily

On 3 October, running_total is the orders of the 1st, 2nd and 3rd added together. That's month to date, for every day at once.

Work out the daily (or monthly) figures in a CTE first, then run the window over them. You can write SUM(COUNT(*)) OVER (…) in one step, but it's harder to read.

Add PARTITION BY to restart the total, for example each year:

SUM(revenue) OVER (PARTITION BY year ORDER BY month) AS ytd_revenue