postgresql - Is there a SQL code for cumulative count of SaaS customer over months?

Question

I have a table with: ID (id client), date_start (subscription of SaaS), date_end (could be a date value or be NULL).

So I need a cumulative count of active clients month by month.

any idea on how to write that in Postgres and achieve this result?

Starting from this, but I don't know how to proceed

select 
date_trunc('month', c.date_start)::date,
count(*) 
from customer

question from:https://stackoverflow.com/questions/65873096/is-there-a-sql-code-for-cumulative-count-of-saas-customer-over-months

Answer 1

Please check next solution:

select 
    subscrubed_date,
    subscrubed_customers,
    unsubscrubed_customers,
    coalesce(subscrubed_customers, 0) - coalesce(unsubscrubed_customers, 0) cumulative
from (
    select distinct
    date_trunc('month', c.date_start)::date subscrubed_date,
    sum(1) over (order by date_trunc('month', c.date_start)) subscrubed_customers
    from customer c
    order by subscrubed_date
) subscribed
left join (
    select distinct
    date_trunc('month', c.date_end)::date unsubscrubed_date,
    sum(1) over (order by date_trunc('month', c.date_end)) unsubscrubed_customers
    from customer c
    where date_end is not null
    order by unsubscrubed_date
) unsubscribed on subscribed.subscrubed_date = unsubscribed.unsubscrubed_date;

share SQL query