Running and rolling metrics
A cumulative total starts at the beginning. A rolling metric uses a moving portion of the sequence. Define the window before naming the metric.
Cumulative totals
A running SUM uses a frame from UNBOUNDED PRECEDING to CURRENT ROW. Each result includes every preceding row in its partition plus the current row.
Moving frames
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW covers up to three rows. Early rows contain fewer observations. The frame size describes rows, not calendar days.
Dates and missing days
To call a metric a seven-day average, ensure your input contains one row per calendar day, including days with zero activity when appropriate. Join to a calendar and aggregate at day grain first.
Ties and explicit frames
With ORDER BY, many aggregates default to a RANGE frame that includes peers. Specify ROWS when you intend a row-by-row cumulative value, and use deterministic ordering for ties.