[FIXED] How to build a churn funnel by dates having start and end date in PostgreSQL

Issue

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

user_id	date	disactivated_at
1	October 28, 2021
2	October 28, 2021
3	October 28, 2021	August 26, 2022
4	October 28, 2021
5	October 28, 2021
6	October 28, 2021	March 29, 2022
7	October 28, 2021
8	October 28, 2021
9	October 28, 2021	August 26, 2022
10	October 28, 2021
11	October 28, 2021	March 30, 2022
12	October 28, 2021
13	October 28, 2021
14	October 28, 2021	February 4, 2022
15	October 28, 2021
16	October 28, 2021
17	October 28, 2021
18	October 28, 2021	January 19, 2022
19	October 28, 2021
20	October 28, 2021

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:

Month	Active_users
November	20
December	20
January	19
February	18
March	16
April	16
May	16
June	16
July	16
August	14
September	14

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).

Solution

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.

Answered By - Stefanov.sm

Answer Checked By - Mary Flores (PHPFixing Volunteer)