Announcing our $3.4M seed round 🚀 Read more →

How to Calculate Cumulative Sum/Running Total in Snowflake in Snowflake

Let's say we want a graph that's always "up and to the right" in Snowflake. Say, a graph of our cumulative sessions by day. First, we'll need a table with a day column and a count column:

select
  to_date(start_date) as day,
  count(1)
from sessions
group by to_date(start_date);
     day    | count
------------+-------
 2020-05-01 | 1     
 2020-05-02 | 4
 2020-05-03 | 2         

Next, we'll write a Snowflake common table expression (CTE) and use a window function to keep track of the cumulative sum/running total:

select
  to_date(start_date) as day,
  count(1)
from sessions
group by to_date(start_date);

with data as (
  select
    to_date(start_date) as day,
    count(1)  as number_of_sessions
  from sessions
  group by to_date(start_date)
)

select
  day,
  sum(number_of_sessions) over (order by day asc rows between unbounded preceding and current row)
from data;
      day    | sum
------------+-------
 2020-05-01 | 1     
 2020-05-02 | 5
 2020-05-03 | 7  

Voila! 📈

Ready for a modern SQL editor?