user3906778 user3906778 - 2 months ago 7
SQL Question

Postgresql count rows per date adding up

I'm trying to make a diagram where I can follow the increasing user numbers.

Therefore I wrote a SQL statement searching for newly added users in a specific period:

select distinct Createtime, count (user_id) as new_users
from user
where createtime between '2015-12-03 00:00:00' and CURRENT_DATE
group by createtime
order by createtime asc;


The following result is shown:

createtime|new_users
---------------------
2015-12-04| 1
2016-01-20| 1
2016-02-03| 5
2016-02-04| 1


I would like to add up the results to have the current number of users for each of the dates, so the result should look more like this:

createtime|new_users
---------------------
2015-12-04| 1
2016-01-20| 2
2016-02-03| 7
2016-02-04| 8


Is there a SQL statement or a different way to achieve this? I hope you can help me with this.

Answer

You can do that with a window function:

select createtime, sum(new_users) over (order by createtime) as new_users
from (
  select createtime::date as createtime, count(user_id) as new_users 
  from "user"
  where createtime between '2015-12-03 00:00:00' and CURRENT_DATE 
  group by createtime 
) t
order by createtime asc;

user is a reserved keyword and needs to be quoted, otherwise you can't use it as an identifier.