Rolling metrics, cumulative sums, moving averages, and period-over-period analytics with frame clauses.
Cumulative calculations are often the first real analytic use case.
SELECT order_date,
amount,
SUM(amount) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM daily_sales;Always specify the frame explicitly when you want a deterministic row-by-row cumulative total.
SELECT event_time,
user_id,
COUNT(*) OVER (
ORDER BY event_time
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_events
FROM events;Helpful for event streams and operational dashboards.
SELECT measured_at,
temperature,
MAX(temperature) OVER (
ORDER BY measured_at
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS max_so_far
FROM sensor_readings;Cumulative max and min are compact ways to model records or thresholds.
Smooth noisy data with bounded frames.
SELECT day,
revenue,
AVG(revenue) OVER (
ORDER BY day
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3
FROM daily_revenue;A row-based moving average is stable when each row is one time bucket.
SELECT day,
revenue,
SUM(revenue) OVER (
ORDER BY day
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_sum_7
FROM daily_revenue;This is a common KPI smoothing pattern for dashboards.
SELECT day,
value,
AVG(value) OVER (
ORDER BY day
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS centered_avg_3
FROM metrics;A centered frame is useful for smoothing but depends on future rows being present.
SELECT month,
region,
sales,
SUM(sales) OVER (
PARTITION BY region
ORDER BY month
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS regional_running_total
FROM monthly_sales;Combining `PARTITION BY` and `ORDER BY` is the backbone of grouped time-series analysis.