How to Calculate Cumulative Sum/Running Total in Snowflake

In this tutorial, we will explore how to calculate a cumulative sum or running total in Snowflake using SQL. This is a common task in data analysis, where you need to compute the cumulative sum of a column's values up to each row.

What is a Cumulative Sum/Running Total?

A cumulative sum (also known as a running total) is the sum of a sequence of numbers, where each number is added to the total sum of the previous numbers. This allows you to see how values accumulate over time or across rows in a dataset.

Using the Window Function in Snowflake

In Snowflake, you can use the SUM() function in combination with a window function to calculate a cumulative sum. The window function is specified using the OVER() clause, which defines the window or partition over which the cumulative sum is calculated.

SQL Syntax

The basic syntax for calculating a cumulative sum in Snowflake is as follows:

SELECT column_name,
       SUM(column_name) OVER (ORDER BY column_name) AS cumulative_sum
FROM your_table;
                                

In the above query:

• SUM(column_name) calculates the sum of the values in the specified column.
• OVER (ORDER BY column_name) defines the window for the cumulative sum calculation. It orders the data based on the column_name and computes the sum up to each row.

Example

Let’s assume we have a table called sales with the following data:

+------------+--------+
| sale_date  | amount |
+------------+--------+
| 2025-01-01 |   100  |
| 2025-01-02 |   150  |
| 2025-01-03 |   200  |
| 2025-01-04 |   50   |
+------------+--------+
                                

To calculate the cumulative sum of the sales amounts, you would run the following query:

SELECT sale_date,
       amount,
       SUM(amount) OVER (ORDER BY sale_date) AS cumulative_sum
FROM sales;
                                

The result would be:

+------------+--------+----------------+
| sale_date  | amount | cumulative_sum |
+------------+--------+----------------+
| 2025-01-01 |   100  |      100       |
| 2025-01-02 |   150  |      250       |
| 2025-01-03 |   200  |      450       |
| 2025-01-04 |   50   |      500       |
+------------+--------+----------------+
                                

Handling Partitions

If you want to calculate the cumulative sum for each group of rows, you can use the PARTITION BY clause. For example, if you want to calculate a cumulative sum for each region separately, you can partition the data by region:

SELECT region,
       sale_date,
       amount,
       SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS cumulative_sum
FROM sales;
                                

Conclusion

Calculating a cumulative sum or running total in Snowflake is straightforward using the SUM() function with a window function. This powerful technique allows you to perform time-based calculations and gain insights into data trends. By utilizing partitions, you can even calculate running totals for each group of data, making this a versatile tool for many data analysis tasks.