Window Aggregations

Running Total

A running total (cumulative sum) accumulates values as you move through rows. Using SUM() OVER with ORDER BY creates a cumulative sum that grows with each row.

-- Running total of orders per customer
SELECT 
    order_id,
    customer_id,
    order_date,
    total_amount,
    SUM(total_amount) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders
ORDER BY customer_id, order_date;

The frame clause ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW specifies that the sum includes all rows from the beginning of the partition up to and including the current row.

-- Running total across all orders (no partition)
SELECT 
    order_id,
    order_date,
    total_amount,
    SUM(total_amount) OVER (
        ORDER BY order_date
    ) AS cumulative_revenue
FROM orders;

When ORDER BY is present in the OVER clause but no frame is specified, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. For running totals with potentially duplicate ORDER BY values, use ROWS BETWEEN explicitly to avoid unexpected behavior.

Running totals are essential for financial reports, inventory tracking, and any scenario where you need to see accumulated values over time.

Moving Average

A moving average smooths out data by averaging a fixed number of preceding and/or following rows. It is calculated using AVG() OVER with a frame specification.

-- 7-day moving average of daily sales
SELECT 
    sale_date,
    daily_sales,
    AVG(daily_sales) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily_sales
ORDER BY sale_date;

The frame ROWS BETWEEN 6 PRECEDING AND CURRENT ROW includes the current row and the 6 rows before it (7 rows total).

-- 3-day moving average (centered: 1 before, current, 1 after)
SELECT 
    sale_date,
    daily_sales,
    AVG(daily_sales) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS moving_avg_centered
FROM daily_sales;

-- Forward-looking moving average
SELECT 
    sale_date,
    daily_sales,
    AVG(daily_sales) OVER (
        ORDER BY sale_date
        ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
    ) AS forward_avg_3d
FROM daily_sales;

Moving averages are widely used in time-series analysis to identify trends, smooth noise, and forecast future values. Common window sizes are 7 (weekly), 30 (monthly), and 365 (yearly).

Frame Specification

The frame specification defines exactly which rows are included in the window function's calculation. It controls the boundaries of the window.

-- Frame with ROWS BETWEEN
SELECT 
    order_date,
    total_amount,
    SUM(total_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
    ) AS five_row_sum
FROM orders;

Frame boundaries:

• UNBOUNDED PRECEDING: First row of the partition
• n PRECEDING: n rows before the current row
• CURRENT ROW: The current row
• n FOLLOWING: n rows after the current row
• UNBOUNDED FOLLOWING: Last row of the partition

-- Cumulative sum from start to current row
SELECT 
    order_date,
    total_amount,
    SUM(total_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative
FROM orders;

-- Total of all rows (entire partition)
SELECT 
    order_date,
    total_amount,
    SUM(total_amount) OVER (
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS partition_total
FROM orders;

ROWS vs RANGE:

• ROWS is physical: counts actual rows
• RANGE is logical: includes all rows with the same ORDER BY value

-- ROWS: exactly 3 rows (may not include all ties)
SUM(amount) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

-- RANGE: includes all rows with same date value
SUM(amount) OVER (ORDER BY date RANGE BETWEEN 2 PRECEDING AND CURRENT ROW)

Always specify the frame explicitly when using ORDER BY in window functions to avoid relying on database-specific defaults.

Common Aggregation Patterns

Window aggregations enable powerful analytical patterns for reporting and data analysis.

Cumulative percentage:

-- Cumulative percentage of total revenue
SELECT 
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY order_date) AS cumulative,
    SUM(daily_revenue) OVER (ORDER BY order_date) / 
    SUM(daily_revenue) OVER () * 100 AS cumulative_pct
FROM daily_revenue;

Running count and min/max:

-- Running count of orders and running minimum order amount
SELECT 
    order_id,
    order_date,
    total_amount,
    COUNT(*) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_count,
    MIN(total_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_min
FROM orders;

Year-to-date calculations:

-- Year-to-date revenue
SELECT 
    order_date,
    total_amount,
    SUM(total_amount) OVER (
        PARTITION BY YEAR(order_date)
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS ytd_revenue
FROM orders;

Percent of partition total:

-- Each order as a percentage of its customer's total spending
SELECT 
    order_id,
    customer_id,
    total_amount,
    total_amount / SUM(total_amount) OVER (
        PARTITION BY customer_id
    ) * 100 AS pct_of_customer_total
FROM orders;

Cumulative distribution:

-- CUME_DIST: fraction of rows with values less than or equal to current row
SELECT 
    product_name,
    price,
    CUME_DIST() OVER (ORDER BY price) AS cumulative_distribution
FROM products;

These patterns replace complex self-joins and subqueries with elegant, single-query solutions.