CODEWITHSUNDEEP
sqlpostgresqlselectcountsum
herrherr
herrherr

Reputation: 698

Postgresql - Cumulative sum of created users

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:

Upvotes: 3

Views: 4841

Answers (1)

Mureinik
Mureinik

Reputation: 311428

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;

Upvotes: 4

Related Questions