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

Next, 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
database icon
Better SQL for the people
Get more done with PopSQL and PostgreSQL