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
From PostgreSQL query to chart to Slack in seconds
Get to answers faster, together, with PopSQL and PostgreSQL