I have a table in PostgreSQL with start date of our customers end date of unsubscribing from our service

How can I have a table, which shows how many active users do we have in each month (or week, or day)?

The desirable result is:

Unfortunately, I don't have any draft of my own code, since I don't know from what to start here. Basic group by method will not work here (it could work if I needed to know the actual number of active users for now, but I also need for each previous period of time).

CodePudding user response：

Complicated a bit but step-by-step straightforward - build a list of months (or weeks, or days) - t_months_list CTE, join it with the list of users with "date"-s trimmed to month (or week, or day) as "month" - the t_list CTE - using "month" and then aggregate with filtering.

with
t_months_list as 
(
 select generate_series('2021-10-01', '2022-09-01', interval '1 month')::date "month"
),
t_list as 
(
 select date_trunc('month', "date")::date "month", "date", desactivated_at 
 from the_table
)
select tml."month",
  count(*) filter 
    (
      where tl."date" is not null 
      and (desactivated_at is null or desactivated_at > tml."month")
    ) as active_users
from t_months_list tml left join t_list tl on tml."month" >= tl."month"
group by tml."month"
order by tml."month";

Demo on db-fiddle
Please note that the use of reserved words (i.e. month, date) as column names is not a good idea.