CODEWITHSUNDEEP
sqlpostgresqlamazon-redshift
Berra2k
Berra2k

Reputation: 328

Need to count rows during a date period then count rows based on what that date period is using other conditions

I am trying to calculate subscription churn. I have a table that looks like the following: 

    subid  | startdate | enddate 
    000001 | 9/26/2016 | 10/26/2016
    000002 | 11/4/2015 | 12/4/2016 
    000003 | 11/18/2016| 12/18/2016 
    000004 | 8/3/2016  | 10/16/2016
    000005 | 7/16/2016 | 11/29/2016

To calculate churn by month I need to create logic that looks like the following:

select 
date_trunc('month',enddate) as month,
count(id), --of all accounts with an enddate of that month
count(id), --of all accounts that have a start date prior to that month and an end date equal to or after that month
from table1

Essentially the goal here is to find the number of subscriptions ending on a given month and also count the number of subscriptions that are still active during that month. I have no idea how to do this within the same group since the second count(id) is conditional to the first.

The result of the example table rows would be this:

date      | count1 | count2
10/1/2016 | 2      | 4
11/1/2016 | 1      | 3
12/1/2016 | 2      | 3

Upvotes: 2

Views: 62

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

You can use correlated sub-queries to get the different counts.

select distinct
date_trunc('month',enddate) as mnth,
(select count(*) from table1 
 where date_trunc('month',enddate) = date_trunc('month',t1.enddate)) count1,
(select count(*) from table1 
 where date_trunc('month',enddate) >= date_trunc('month',t1.enddate) 
 and date_trunc('month',startdate) <= date_trunc('month',t1.enddate)) count2 
from table1 t1
order by 1

Another way is to self-join.

select 
date_trunc('month',t1.enddate) as mnth,
count(distinct t1.subid) c1,
count(distinct t2.subid) c2
from table1 t1
left join table1 t2 on date_trunc('month',t2.enddate)>= date_trunc('month',t1.enddate) and date_trunc('month',t2.startdate)<= date_trunc('month',t1.enddate)
group by date_trunc('month',t1.enddate)
order by 1

Sample Demo

Upvotes: 2

Related Questions