for each month, count entries with interval

Question

i'm having hard times with creating a statistics with sum of ongoing subscriptions per month

i have table subscriptions

id | created_at          | cancelled_at
----------------------------------------
 1 | 2020-12-29 13:56:12 | null
 2 | 2021-02-15 01:06:25 | 2021-04-21 19:35:31
 3 | 2021-03-22 02:42:19 | null
 4 | 2021-04-21 19:35:31 | null

and statistics should look as follows:

month   | count
---------------
12/2020 | 1     -- #1
01/2021 | 1     -- #1
02/2021 | 2     -- #1 + #2
03/2021 | 3     -- #1 + #2 + #3
04/2021 | 3     -- #1 + #3 + #4, not #2 since it ends that month
05/2021 | 3     -- #1 + #3 + #4

so far i was able to make list of all months i need the stats for:

select generate_series(min, max, '1 month') as "month"
from (
    select date_trunc('month', min(created_at)) as min,
    now() as max
    from subscriptions
) months;

and get the right number of subscriptions for specific month

select sum(
    case 
        when
            make_date(2021, 04, 1) >= date_trunc('month', created_at) 
            and make_date(2021, 04, 1); < date_trunc('month', coalesce(cancelled_at, now() + interval '1 month'))
        then 1
        else 0
    end
) as total
from subscriptions
-- returns 3

but i am struggling combining those together... would OVER (which i am unexperienced with) be of any use for me? i found Count cumulative total in Postgresql but it's different case (dates are fixed)... or is the proper approach to use function with FOR somehow?

score 1 · Accepted Answer · answered May 26 '21 at 00:26

You can use generate_series() to generate the months and then a correlated subquery to calculate the actives:

select yyyymm,
       (select count(*)
        from subscriptions s
        where s.created_at < gs.yyyymm + interval '1 month' and
              (s.cancelled_at > gs.yyyymm + interval '1 month' or s.cancelled_at is null)
       ) as count
from generate_series('2020-12-01'::date, '2021-05-01'::date, interval '1 month'
                    ) gs(yyyymm);

for each month, count entries with interval

1 Answers1