Computing a cumulative sum in BigQuery

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!

No alt text provided for this image

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