#Calculate cumulative sum in SQL

Calculating cumulative sums or running totals is a common task in data analysis, especially when dealing with time series data or analyzing sequential patterns. In SQL, this can be achieved using window functions, providing a powerful way to perform complex aggregations. Here is how to calculate cumulative sums, percentages, and group-wise cumulative sums in SQL.

##Create a sample table

Let’s start by creating a small example table from a BigQuery public dataset, namely an extract of Google Analytics data from July 2017.

CREATE OR REPLACE TABLE `training.sql_cumsum`  AS (
    SELECT 
        PARSE_DATE("%Y%m%d", date) AS day,
        EXTRACT(WEEK FROM PARSE_DATE("%Y%m%d", date)) AS week,
        SUM(totals.visits) AS sessions
    FROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*` 
    GROUP BY 1,2
    ORDER BY 1
    LIMIT 10
);

SELECT * FROM `training.sql_cumsum`;

##Running total

A running total, or cumulative sum, is the summation of a sequence of numbers that is updated each time a new number is added to the sequence. It's a common operation in time series analysis to understand trends over time.

To compute the running sum in SQL, use the window clause SUM(metric) OVER (ORDER BY dimension):

SELECT 
    day,
    sessions,
    -- Running total of daily sessions
    SUM(sessions) OVER (ORDER BY day) AS sessions_cumtd
FROM `training.sql_cumsum`
ORDER BY day
LIMIT 10

This query will return the daily sessions along with the cumulative sum, ordered by the date.

##Running percentage of total

Sometimes, it's useful to understand the cumulative percentage of a total, especially when analyzing proportions or contributions of parts to a whole. This can be achieved by dividing the cumulative sum by the total with SUM(metric) OVER():

SELECT 
    day,
    sessions,
    -- Cumulated percentage of daily sessions
    SUM(sessions) OVER (ORDER BY day) / SUM(sessions) OVER() AS sessions_pct_cumtd, 
FROM `training.sql_cumsum`
ORDER BY day

##Cumulated sum, by group

In many scenarios, you may want to calculate the cumulative sum within specific groups or categories. This can be done using the PARTITION BY clause, allowing you to segment the data and calculate the cumulative sum within each segment.

SELECT 
    day,
    sessions,
    week,
    -- Cumulated sum of daily sessions
    SUM(sessions) OVER (ORDER BY day) AS sessions_cumtd,
    -- Cumulated sum of sessions, partitioned by week
    SUM(sessions) OVER (PARTITION BY week ORDER BY day) AS sessions_cumtd_week
FROM `training.sql_cumsum`
ORDER BY day

Calculating cumulative sums in SQL is a powerful technique that allows you to analyze trends, patterns, and sequences in your data. By leveraging window functions and the PARTITION BY clause, you can create complex aggregations tailored to your specific needs.