Postgresql - Cumulative sum of created users

Question

I have a users table with a timestamp when each user was created. I'd like to get the cumulative sum of users created per month.

I do have the following query which is working, but it's showing me the sum on a per day basis. I have a hard time going from this to a per month basis.

SELECT 
    created_at,
    sum(count(*)) OVER (ORDER BY created_at) as total
FROM users
GROUP BY created_at

Expected output:

created_at  count
-----------------
2016-07  100
2016-08  150
2016-09  200
2016-10  500

Former reading:

Can you show us your expected output? There is more than one way to interpret your question. — Tim Biegeleisen
Possible duplicate of Calculating Cumulative Sum in PostgreSQL — moooeeeep
I have added the expected output. The referred question is not answering my question, as there is no per month grouping done there. — herrherr
It's not 1:1 the same problem, but in the last code snippet of the accepted answer there you find almost exactly the answer you accepted here. — moooeeeep

Mureinik Mureinik · Accepted Answer · 2018-01-22T07:49:52

I'd take a two-step approach. First, use an inner query to count how many users were created each month. Then, wrap this query with another query that calculates the cumulative sum of these counts:

SELECT   created_at, SUM(cnt) OVER (ORDER BY created_at ASC)
FROM     (SELECT   TO_CHAR(created_at, 'YYYY-MM') AS created_at, COUNT(*) AS cnt
          FROM     users
          GROUP BY TO_CHAR(created_at, 'YYYY-MM')) t
ORDER BY 1 ASC;

Postgresql - Cumulative sum of created users

1 Answers