SQL Window Functions: Running Totals, Moving Averages, and Cumulative Metrics

SELECT order_date,
       amount,
       SUM(amount) OVER (
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM daily_sales;

# Cumulative sum ordered by date.

SELECT event_time,
       user_id,
       COUNT(*) OVER (
         ORDER BY event_time
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_events
FROM events;

# Count rows seen so far.

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;

# Track the best-so-far value.

SELECT day,
       revenue,
       AVG(revenue) OVER (
         ORDER BY day
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_avg_3
FROM daily_revenue;

# Average current row and previous two rows.

SELECT day,
       revenue,
       SUM(revenue) OVER (
         ORDER BY day
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS moving_sum_7
FROM daily_revenue;

# Rolling weekly sum on daily rows.

SELECT day,
       value,
       AVG(value) OVER (
         ORDER BY day
         ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
       ) AS centered_avg_3
FROM metrics;

# Use prior and following rows together.

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;

# Reset the running total by partition.