Level 3 · Year-to-date revenue

Aggregates like SUM, AVG, COUNT, MIN and MAX can all be used as window functions. With an ORDER BY inside OVER, they become cumulative:

SUM(net_revenue) OVER (ORDER BY month) AS ytd_revenue

For each month, this sums the revenue of every month up to and including it: January’s YTD is January, February’s is January plus February, and so on. The rows are not collapsed, so you get the monthly number and the running total side by side.

Aggregate first, then window

Revenue lives on order lines, but the running total is over months. Aggregate to one row per month in a CTE, then apply the window on top. If you put the window directly over line-level rows, you would get a running total per line, which is not what anyone wants on a slide.

Frames: what “up to the current row” means

Every window with an ORDER BY has a frame: the set of rows the function sees for the current row. The default frame when you write only ORDER BY is:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

RANGE treats rows with the same ORDER BY value as peers and includes all of them. With one row per month that makes no difference. But if you ran a cumulative sum over orders ordered by date and two orders shared a date, both would show the same running total, including each other. That surprises people. Writing the frame explicitly with ROWS makes it row-by-row:

SUM(net_revenue) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Being explicit costs nothing and documents intent. This default applies in Postgres, DuckDB, MySQL 8, BigQuery and Snowflake alike.

Resetting the total

PARTITION BY restarts the running total per group. To have a YTD that resets each year over several years of data:

SUM(net_revenue) OVER (
  PARTITION BY EXTRACT(YEAR FROM month)
  ORDER BY month
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS ytd_revenue

Or per channel, per category, per customer: whatever the business tracks separately.

Share of total

The same function with an empty OVER () gives the grand total on every row, which is the standard way to compute a share of total without a subquery:

ROUND(100.0 * net_revenue / SUM(net_revenue) OVER (), 1) AS pct_of_year

Common mistakes

• Forgetting ORDER BY inside OVER: SUM(x) OVER () is the grand total, not a running total.
• Relying on the outer ORDER BY. The outer one sorts the output; only the ORDER BY inside OVER defines the running order.
• Rounding monthly values before summing them, which makes the YTD drift by cents from the true total.