Count number of members at the end of each year-CodePudding

I have a table with the following fields: user_id, join_date, leave_date. Note: There are some years that have no joiners or leavers, and if they are still a member then the leave_date is NULL.

I am trying to retrieve the total number of members at the end of each calendar year from 2010 to today.

I have tried to write this for the most recent year only using the following code:

SELECT COUNT(user_id) 
FROM memberships 
WHERE join_date < '2021-12-31' AND leave_date is null

CodePudding user response：

With Years 
As
(
    Select min(year(join_date)) as [Year]
    From members
    Union All
    Select Y.[Year]   1
    From Years Y
    Where Y.[Year]   1 <= Year(GetDate()) 
)
Select [Year], 
    COUNT(*) As [End Year Member Count]
From Years
    Left Join members On [Year] Between Year(members.join_date) 
        And (Year(IsNull(leave_date, getdate()   365)) - 1)
    Group By [Year]

CodePudding user response：

yeah, how about a query like below

; with date_cte as 
(
select cast('2010-12-31' as date) as lastdate, cast('2010-01-01' as date) as prev_lastdate
union all
select dateadd(YY,1,lastdate) as lastdate, dateadd(YY,1,prev_lastdate) as prev_lastdate
from date_cte where  dateadd(YY,1,lastdate) <getutcdate()
)


select 
    lastdate,count(user_id) as count_members
from memberships 
right join date_cte
on  join_date  <= lastdate 
    and (leave_date is null or leave_date >lastdate)
group by lastdate

I ran tests against this data

drop table if exists memberships ;
create table memberships (user_id int , join_date date, leave_date date) ;
insert into memberships values
(1,'2009-01-01','2015-09-08'),
(2,'2009-01-01','2016-09-08'),
(3,'2012-01-01','2013-09-08'),
(4,'2013-01-01','2013-07-08'),
(5,'2017-01-01',NULL);
select * from memberships

results are:

lastdate    count_members

2010-12-31  2
2011-12-31  2
2012-12-31  3
2013-12-31  2
2014-12-31  2
2015-12-31  1
2016-12-31  0
2017-12-31  1
2018-12-31  1
2019-12-31  1
2020-12-31  1