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 | 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! 📈
From Snowflake query to chart to Slack in seconds
Get to answers faster, together, with PopSQL and Snowflake