- Explore
- Collaborate
- Visualize
- Connect
- Pricing
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
From Snowflake query to chart to Slack in seconds
Get to answers faster, together, with PopSQL and Snowflake