3

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:

Mureinik
  • 297,002
  • 52
  • 306
  • 350
herrherr
  • 708
  • 1
  • 9
  • 26
  • Can you show us your expected output? There is more than one way to interpret your question. – Tim Biegeleisen Jan 22 '18 at 07:36
  • Possible duplicate of [Calculating Cumulative Sum in PostgreSQL](https://stackoverflow.com/questions/22841206/calculating-cumulative-sum-in-postgresql) – moooeeeep Jan 22 '18 at 07: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 Jan 22 '18 at 07: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 Jan 22 '18 at 09:25

1 Answers1

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;
Mureinik
  • 297,002
  • 52
  • 306
  • 350