Standard aggregates — SUM, AVG, COUNT, MIN, MAX, and their statistical variants — become window functions when used with an OVER clause, returning one value per row without collapsing the result set. The most common applications are running totals via SUM(x) OVER (ORDER BY ts), cumulative averages via AVG(x) OVER (ORDER BY ts ROWS UNBOUNDED PRECEDING), and moving averages over a fixed-width trailing window such as the seven-day average AVG(x) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). Frame sizes are counted in physical rows with ROWS, and centered windows expressed as BETWEEN N PRECEDING AND N FOLLOWING include 2N+1 rows around the current row, useful for symmetric smoothing.
Comparing window aggregates to plain aggregates over the entire partition reveals several useful patterns. SUM(x) OVER () returns the grand total on every row, and dividing by it yields percent-of-total without a separate subquery. SUM(x) OVER (PARTITION BY g) returns the group total, supporting percent-of-group calculations. COUNT(*) OVER () returns total row count per row, which is convenient for pagination metadata in a single query without needing a second SELECT. Running maximum and minimum are achieved via MAX and MIN with ROWS UNBOUNDED PRECEDING, and these are handy for tracking all-time highs and lows within a partition up to the current row. Snowflake additionally offers RATIO_TO_REPORT(x) OVER (PARTITION BY g) as a built-in shortcut for the x-over-group-total calculation.
Conditional aggregation in windows is supported via the FILTER clause, for example SUM(x) FILTER (WHERE active) OVER (...), which is cleaner than wrapping in CASE and may help the optimizer prune work. Statistical aggregates including STDDEV_SAMP, STDDEV_POP, VAR_SAMP, VAR_POP, and even CORR plus the REGR_SLOPE and REGR_INTERCEPT regression functions are usable as window functions. A practical limitation: window functions do not support DISTINCT directly, so cumulative distinct counts require a workaround such as windowing over a first-occurrence flag (where ROW_NUMBER() = 1 acts as 1/0) and summing that flag across the frame. The same pattern applies to rolling unique-user counts over time-based windows.