Calculating Cumulative Sums with Window Functions: SUM(), AVG()

Let’s imagine: you’re tracking your company’s revenue, online store sales, or even just analyzing your own yearly spending. You don’t just want to see income or expenses for each month, but also how they add up from month to month.

Regular aggregate functions (GROUP BY) won’t help here — they’ll group your data and return one row per group. But what if you want to see every month and also calculate the running total? That’s where SUM() with window functions comes to the rescue.

Window Function Basics for Cumulative Sums

Window functions let you perform aggregate operations over window frames. This way, you can sum values for each row without removing other rows. No more sacrifices for GROUP BY!

Syntax for SUM() with a window function

Here’s a basic template for calculating a cumulative sum:

SELECT
    column_name,
    SUM(column_name) OVER (PARTITION BY partition_column ORDER BY order_column) AS cumulative_sum
FROM 
    table_name;

Here:

• SUM(column_name) — sums up the values.
• OVER() — sets the window for calculation.
• PARTITION BY — splits data into groups (optional).
• ORDER BY — sets the row order inside the window.

Example: Cumulative Revenue by Month

Imagine a table of your revenues:

We want to see the revenue for each month and the cumulative total. Let’s write an SQL query:

SELECT
    month,
    revenue,
    SUM(revenue) OVER (ORDER BY month) AS cumulative_revenue
FROM 
    revenues;

Result:

What’s happening:

1. ORDER BY month inside OVER() tells PostgreSQL to process rows in chronological order.
2. For each row, the sum is calculated including all previous rows (and the current one).

Think carefully about what’s going on here. For the first row, SUM() sums just the 1st row, for the second — the sum of two rows, for the third — the sum of three rows. That’s why the order of months is so important!

Example: Cumulative Revenue by Region

If you had a sales table by region, part of it might look like this:

Now we want to calculate the cumulative revenue separately for each region:

SELECT
    region,
    month,
    revenue,
    SUM(revenue) OVER (PARTITION BY region ORDER BY month) AS cumulative_revenue
FROM 
    sales;

The result will be:

Now each region is analyzed separately (PARTITION BY region), but inside each region, rows are ordered by time (ORDER BY month).

Moving Average (AVG())

Okay, cumulative sums are cool, but what if you want to analyze trends, say, for the last 3 months? That’s where moving average comes in handy.

Example: Moving Average of Revenues

We’re working again with the revenues table, and here’s its data:

Query to calculate a 3-month moving average:

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

Result:

Explanation:

1. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW tells PostgreSQL to look at the current row and two previous rows to calculate the average.
2. So for each month, we see the average revenue for the last 3 months.

So for each row, we set a window of 3 rows: current and two previous. Then we calculate the average for them. Super handy.

How ORDER BY Works and Why It Matters

Window functions depend on the correct row order. If the order is wrong (or not set at all), results can be totally unexpected.

Example: Mistakes from Missing ORDER BY

If we remove ORDER BY from OVER(), instead of a cumulative sum we’ll get the sum of all revenues for every row:

SELECT
    month,
    revenue,
    SUM(revenue) OVER () AS wrong_cumulative_sum
FROM 
    revenues;

Result:

The rows aren’t ordered, and instead of a cumulative sum, the function just sums all rows for every result.

Real-World Use Cases

Revenue Analysis:

• Cumulative sums let you track how sales or company revenue are growing.
• Moving average helps you see the “clean” trend without noise.

Financial Modeling:

Banks and financial companies use window functions to analyze payments, debt growth, and other metrics.

Building Time Series:

Time-based data like online users, page views, revenue, etc. are perfect for analyzing with SUM() and AVG().