How to Calculate Cumulative Sum-Running Total in Redshift

Let's say we have a sales table in Redshift and we want to see a hockey stick graph of our sales quantity by day. First, we'll need a table with a day column and a count column:

select
  date(sales_date) as day,
  sum(qty) as total_sold
from sales
group by day;

day        | total_sold
-----------+------------
2018-12-01 |	10
2018-12-02 |	40
2018-12-03 |	40
2018-12-04 |	10
2018-12-05 |	30
2018-12-06 |	20
2018-12-07 |	35
2018-12-08 |	30

Next, we'll write a Redshift common table expression (CTE) and use a windowed SUM()function to get the cumulative sum/running total:

with _data as (
  select
    date(sales_date) as day,
    sum(qty) as total_sold
  from sales
  group by day
)
select
  day,
  total_sold,
  sum(total_sold) over (order by day rows unbounded preceding) as cumulative_sum
from _data;

day        | total_sold | cumulative_sum
-----------+------------|----------------
2018-12-01 |	10      |	10
2018-12-02 |	40      |	50
2018-12-03 |	40      |	90
2018-12-04 |	10      |	100
2018-12-05 |	30      |	130
2018-12-06 |	20      |	150
2018-12-07 |	35      |	185
2018-12-08 |	30      |	215
database icon
Better SQL for the people
Get more done with PopSQL and Redshift