SQL for selecting YTD active member by month

November 9, 2022

I need some help in writing a query which will return the distinct count of memberid who are active YTD and by each month

i.e.

202201 – distinct memberid who are active in 202201

202202 – distinct memberid who are active between 202201 – 202202

202203 – distinct memberid who are active between 202201 – 202203

the data structure are similar to below.

memberid	yearmonth	activestatus
1	202201	Y
1	202202	Y
1	202203	N
2	202201	N
2	202202	N
2	202203	Y
3	202201	N
3	202202	Y
3	202203	Y

Thanks you.

Expected:
| yearmonth | active_status |
|———–|—————|
| 202201 | 1 |
| 202202 | 2 |
| 202203 | 3 |

>Solution :

We’re looking for a distinct running count. We can achieve it by using row_number() and count().

select distinct yearmonth                 
      ,count(rn) over(order by yearmonth) as distinct_running_count
from
(
select *
      ,case when row_number() over(partition by memberid order by yearmonth) = 1 then 1 end as rn
from   t
where  activestatus = 'Y'
) t