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!
Bonus point: You can also use a named window declaration for cleaner code.
Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.