PostgreSQL

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. 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 |     10

Next, we’ll write a 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