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