Cumulative Sum with Postgre SQL using date truncating-CodePudding

I'm relatively new to using SQL in Apache Superset and I'm not sure where to look or how to solve my problem.

The short version of what I am trying to do is add a column of cumulative sum based on the total number of users by month.

Here is my PostgreSQL query so far:

SELECT 
    DATE(DATE_TRUNC('month', crdate)) AS "Month", 
    COUNT(DISTINCT user_id) AS "COUNT_DISTINCT(user_id)"
FROM 
    datasource 
WHERE 
    user_id IS NOT NULL
GROUP BY  
    DATE(DATE_TRUNC('month', create))
ORDER BY 
    "COUNT_DISTINCT(user_id)" DESC

Sum of Users by Month

CodePudding user response：

There are some syntax errors, you can't order by an alias and in group by your date column is wrong, so it should be like this:

SELECT 
    DATE(DATE_TRUNC('month', crdate)) AS "Month", 
    COUNT(DISTINCT user_id) AS "COUNT_DISTINCT(user_id)"
FROM 
    datasource 
WHERE 
    user_id IS NOT NULL
GROUP BY  
    DATE(DATE_TRUNC('month', crdate)) AS "Month"
ORDER BY 
    COUNT_DISTINCT(user_id) desc

CodePudding user response：

You can use your query a Basis for the Window function

CREATE TABLE datasource(crdate timestamp,user_id int)

WITH CTE AS (
SELECT
     DATE_TRUNC('month',"crdate") as "Month",
    COUNT(DISTINCT user_id) AS "COUNT_DISTINCT(user_id)"
FROM 
    datasource 
WHERE 
    user_id IS NOT NULL
GROUP BY  
    DATE_TRUNC('month', "crdate")
)
SELECT "Month", SUM("COUNT_DISTINCT(user_id)") OVER (ORDER BY "Month") as cumultatove_sum
FROM CTE

Month | cumultatove_sum
:---- | --------------:

db<>fiddle here