How to Calculate Cumulative Sum-Running Total in PostgreSQL
Let's say we want to see a hockey stick graph of our cumulative user sign ups by day in PostgreSQL. First, we'll need a table with a day column and a count column:
select
date_trunc('day', created_at) as day,
count(1)
from users
group by 1 day | count
---------------------+-------
2018-01-01 00:00:00 | 10
2018-01-02 00:00:00 | 10
2018-01-03 00:00:00 | 10Next, we'll write a PostgreSQL common table expression (CTE) and use a window function to keep track of the cumulative sum/running total:
with data as (
select
date_trunc('day', created_at) as day,
count(1)
from users
group by 1
)
select
day,
sum(count) over (order by day asc rows between unbounded preceding and current row)
from data day | sum
---------------------+-----
2018-01-01 00:00:00 | 10
2018-01-02 00:00:00 | 20
2018-01-03 00:00:00 | 30