Running Totals & Cumulative

Advanced SQL

Cumulative Calculations in SQL

Running totals and cumulative calculations answer: "What's the total so far?" and "How does each row compare to all previous rows?" These are essential for financial reports, trend analysis, and cumulative metrics.

Running Total — SUM() OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
Moving Average — AVG() OVER (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
Cumulative % — Running total / grand total
CDF — CUME_DIST() and PERCENT_RANK()

The key distinction is ROWS vs RANGE in the window frame clause — using the wrong one produces incorrect results when there are ties.

Window Frame Clause

Basic Running Total

-- Simple running total
SELECT
  sale_date,
  revenue,
  SUM(revenue) OVER (
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM daily_sales
ORDER BY sale_date;

ℹ️

Key Insight: Always use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for a true running total. Without the frame clause, PostgreSQL defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW which includes all peers (ties in ORDER BY).

Running Total by Partition

-- Running total per department (resets per dept)
SELECT
  department_id, employee_id, salary,
  SUM(salary) OVER (
    PARTITION BY department_id
    ORDER BY hire_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS dept_running_total,
  SUM(salary) OVER (
    ORDER BY hire_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS company_running_total
FROM employees
ORDER BY department_id, hire_date;

Cumulative Percentage

-- Calculate cumulative percentage of total
SELECT
  department_id, employee_id, salary,
  SUM(salary) OVER w AS running_total,
  SUM(salary) OVER () AS grand_total,
  ROUND(SUM(salary) OVER w * 100.0 / SUM(salary) OVER (), 2) AS cumulative_pct
FROM employees
WINDOW w AS (
  ORDER BY salary DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
ORDER BY salary DESC;

Running Average (Moving Averages)

-- Moving averages with different windows
SELECT
  sale_date, revenue,
  AVG(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7day,
  AVG(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS ma_30day,
  AVG(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS ma_centered_7day
FROM daily_sales
ORDER BY sale_date;

Cumulative Sum with Reset at Boundaries

-- Running total that resets at month boundaries
SELECT
  department_id, employee_id, salary, hire_date,
  SUM(salary) OVER (
    PARTITION BY department_id
    ORDER BY hire_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS dept_running_total,
  -- Reset at month boundaries
  SUM(salary) OVER (
    PARTITION BY department_id, DATE_TRUNC('month', hire_date)
    ORDER BY hire_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS monthly_running_total
FROM employees
ORDER BY department_id, hire_date;

⚠️

Common Mistake: Using RANGE instead of ROWS in the frame clause can produce unexpected results when there are ties in the ORDER BY column. ROWS is generally safer for running totals — it always includes exactly the specified number of physical rows.

Running Maximum and Minimum

-- Running min/max with window functions
SELECT
  sale_date, revenue,
  MAX(revenue) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_max,
  MIN(revenue) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_min,
  revenue - MIN(revenue) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS above_running_min,
  MAX(revenue) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - revenue AS below_running_max
FROM daily_sales
ORDER BY sale_date;

Cumulative Distribution Function

-- CDF and percentile rank calculations
SELECT
  employee_id, salary,
  CUME_DIST() OVER (ORDER BY salary) AS cumulative_distribution,
  PERCENT_RANK() OVER (ORDER BY salary) AS percentile_rank,
  NTILE(100) OVER (ORDER BY salary) AS percentile_bucket
FROM employees
ORDER BY salary;

Running Total with Gaps

-- Handle gaps in data with running total
WITH date_series AS (
  SELECT generate_series(
    MIN(sale_date), MAX(sale_date), '1 day'::INTERVAL
  )::DATE AS date
  FROM daily_sales
)
SELECT
  ds.date,
  COALESCE(s.revenue, 0) AS revenue,
  SUM(COALESCE(s.revenue, 0)) OVER (
    ORDER BY ds.date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM date_series ds
LEFT JOIN daily_sales s ON ds.date = s.sale_date
ORDER BY ds.date;

Quiz: Test Your Knowledge

Follow-Up Questions

What's the difference between ROWS and RANGE in running total calculations?
How would you calculate a running total that handles NULL values?
Explain how to compute a running geometric mean.
How do you reset running totals at specific intervals?
What's the best approach for running totals in partitioned tables?

Key Takeaways

Always specify ROWS — ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for true running totals
PARTITION BY resets — running total starts fresh at each partition boundary
Moving average — ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for 7-day MA
Cumulative % — running total / grand total × 100
CUME_DIST() — returns the proportion of values ≤ current value
RANGE vs ROWS — RANGE includes ties; ROWS is exact row count

Running Totals & Cumulative — Window Frames, Moving Averages, Cumulative Distribution