PopSQL

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! 📈

database icon
Shared queries and folders ✅ Version history ✅ One-click connection to Snowflake ✅
Get more done, together, with PopSQL and Snowflake