How to Calculate Cumulative Sum/Running Total 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 | 2Next, 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 | 7Voila! 📈
