Running Totals and Moving Averages

SELECT month, revenue,
       SUM(revenue) OVER (
         ORDER BY month
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM monthly_sales;

SUM() OVER with ORDER BY creates a running total. "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" is the default frame — it sums everything from the first row to the current row.

SELECT month, revenue,
       AVG(revenue) OVER (
         ORDER BY month
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_avg_3m
FROM monthly_sales;

"2 PRECEDING AND CURRENT ROW" means: current row + 2 rows before = 3-row window. Moving averages smooth out spikes and reveal trends — commonly used in sales and marketing analytics.

SELECT customer_id, order_date, order_id,
       COUNT(*) OVER (
         PARTITION BY customer_id
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS order_number
FROM orders;

This gives each order a sequential number per customer — showing whether it's the 1st, 2nd, 3rd order. Useful for cohort analysis and repeat purchase tracking.

-- ROWS: physical rows (faster, more predictable)
SUM(salary) OVER (
  ORDER BY salary
  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)

-- RANGE: logical range of values (groups tied values)
SUM(salary) OVER (
  ORDER BY salary
  RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
)

ROWS counts physical rows. RANGE groups rows with identical ORDER BY values. For most analytics use cases, ROWS is the right choice — predictable and faster.

SELECT year, month, revenue,
       SUM(revenue) OVER (
         PARTITION BY year
         ORDER BY month
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS ytd_revenue
FROM monthly_sales;

PARTITION BY year resets the running total for each year. This is the Year-to-Date (YTD) pattern — standard in financial reporting.