Reputation: 101
I'm trying to find the number of active subscriptions in a month over a timescale.
Table: Subscriptions Fields:
A subscription is considered active at a particular timestamp when:
Example:
This is what I tried:
select "Month", sum(sub) over (order by "Month" asc) as "Active subscriptions"
from
(select to_char(subscriptions."creationDate" at time zone '-7', 'YYYY-MM') as "Month",
count(distinct subscriptions.id) as sub
from subscriptions
where (to_char(subscriptions."deletionDate" at time zone '-7', 'YYYY-MM') is null
or to_char(subscriptions."deletionDate" at time zone '-7', 'YYYY-MM') >= to_char(subscriptions."creationDate" at time zone '-7', 'YYYY-MM') )
group by "Month") as foo
However, the issue with this is that it is including the count of non-active subscriptions in the previous month. To illustrate what I mean, my query above seems to include Subscription B (in example above) as an active subscription in July 2014.
I'm not sure how to get my "Active subscriptions" for a particular month to remove the count of subscriptions that are no longer active in the previous months.
Thank you! :)
Upvotes: 3
Views: 635
Reputation: 19362
SELECT m, count(subscriptions.*)
FROM subscriptions
JOIN generate_series('2010-01-01'::date, now(), interval '1 mon') AS m
ON m >= subscriptions.creationDate AND
(subscriptions.deletionDate IS NULL OR m <= subscriptions.deletionDate)
/* You may get better indexed performance if you use a date
far in the future for current
accounts, instead of NULL. Then you can use BETWEEN */
GROUP BY m ORDER BY m;
Upvotes: 5