Running total

Explore this snippet with some demo data here.

Description

Running totals are relatively simple to calculate in Snowflake, 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>

where

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:

then the query is:

-- RAW
select streams, day, artist from PUBLIC.SPOTIFY_DAILY_TRACKS

select
  sum(streams) over (partition by artist order by day asc) running_total,
  artist,
  day
from RAW
order by 2,3