Explore this snippet with some demo data here.
Running totals are relatively simple to calculate in BigQuery, using the
sum window function.
The template for the query is
select sum(<value>) over (partition by <fields> order by <ordering> asc) running_total, <fields>, <ordering> from <table>
value- this is the numeric quantity to calculate a running total for
fields- these are zero or more columns to split the running totals by
ordering- this is the column which determines the order of the running total, most commonly temporal
table- where to pull these columns from
Using total Spotify streams as an example data source, let’s identify:
value- this is
fields- this is just
ordering- this is the
table- this is called
then the query is: