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
– Tim Biegeleisen, Commented Jan 22, 2018 at 7:36
Possible duplicate of Calculating Cumulative Sum in PostgreSQL — moooeeeep
– moooeeeep, Commented Jan 22, 2018 at 7:36
I have added the expected output. The referred question is not answering my question, as there is no per month grouping done there. — herrherr
– herrherr, Commented Jan 22, 2018 at 7:43
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
– moooeeeep, Commented Jan 22, 2018 at 9:25

Mureinik · Accepted Answer · 2018-01-22 07:49:52Z

4

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;

answered Jan 22, 2018 at 7:49

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

herrherr Over a year ago

Much simpler than I thought, couldn't see the wood for the trees anymore :)

Collectives™ on Stack Overflow

Postgresql - Cumulative sum of created users

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related