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

How to Avoid Gaps in Data

How to Calculate Percentiles

From Snowflake query to chart to Slack in seconds

Get to answers faster, together, with PopSQL and Snowflake