Datawise
BigQuery Window Functions

Computing a cumulative sum in BigQuery

Constantin LunguUpdated 1 min read

Photo by Antoine Dautry on Unsplash

How do you compute a cumulative SUM in BigQuery?

Today we're going to look at how to compute a cumulative sum in BigQuery, a scenario that pops up now and then and is quite easy to solve using window functions.

In the below example, we have a dataset representing customer orders. We'd like to find out the cumulative sum of each individual customer.

For this we'll need :
- SUM function combined with a WINDOW function call
- PARTITION BY customer ID to perform calculation at customer level
- ORDER BY order_date (ascending by default) so that the values are summed up chronologically
- a window frame clause: ROWS BETWEEN UNBOUNDED (starting with the first entry) AND CURRENT ROW (until and including this row)

See below for an illustration of how it all works. Happy querying!

Input data: an orders table with customer_id, order_id, order_total and order_date; Customer-1 has orders of 100, 75 and 90 and Customer-2 orders of 80, 120 and 50, on 2021-01-01, 2021-01-02 and 2021-01-03.

SELECT

customer_id,
order_id,
order_total,
order_date,

SUM(order_total) OVER (PARTITION BY customer_id
                       ORDER BY order_date
                       ROWS BETWEEN UNBOUNDED PRECEDING
                                    AND CURRENT ROW) AS cumulative_sum

FROM input_data

Query results: the same six orders with a cumulative_sum column (boxed in red) of 100, 175, 265 for Customer-1 and 80, 200, 250 for Customer-2.

Bonus point: You can also use a named window declaration for cleaner code.

Enjoyed this? Here are some related articles you might find useful: