Best way to create a client sign up cohort

Question

Hi all,

I'm trying to create a table that shows clients sign up cohort, but I'm having a little trouble figure out the best way. I'm not sure if over partition is the way to go:

Here is the table:

create table clients( userid varchar (25)
,signup_clients date
,client_Status varchar(25)
,States varchar(25)
)
;
insert into clients values
('10HKT','03/05/2020','Active','TX'),
('HOPSR','04/18/2020','Active','CA'),
('FROPT','06/30/2019','Active','UT'),
('JHYTR','03/15/2020','Active',' MA'),
('UPIKR','09/17/2019','Active','FL'),
('STIOR','03/17/2020','Active', 'GA'),
('DFERT','06/1/2020','Active', 'MA'),
('KLYOP','05/12/2019','Active', 'OR'),
('UYPRD','05/22/2019','Active', 'ID'),
('XPOPF','01/01/2020','Active', 'KS'),
('34FUP','04/01/2019','Active', 'UT'),
('ER9UT','02/11/2019','Active', 'KY'),
('5TOPT','04/30/2020','Active', 'LA'),
('KLYOP','10/10/2020','Cancelled','OR'),
('HOPSR','06/10/2020','Cancelled','CA'),
('UPIKR','11/02/2020','Cancelled','FL')

I would love to have a table that looks like this:

Cohort_Month Retention_Month End_Retention. cohort_size
1/1/20 0 12/31/2019
1/1/20 1 1/31/2020
2/1/20 2 2/28/2020.
2/1/20 3 2/28/2020.
3/1/20 4 3/31/2020
3/1/20 5 4/30/2020

Thanks for you help!

Answer 1

Hi @learning_step_by_step

I followed the whole process of you and ErlandSommarskog, and I am still very confused about your question.
The solution I tried：

;with cte   
as(select year(signup_clients) yy,month(signup_clients) mm,min(signup_clients) Cohort_Month,max(signup_clients)End_Retention,  
count(signup_clients) cohort_size  
from clients   
group by year(signup_clients),month(signup_clients))  
  
select Cohort_Month,Retention_Month=month(End_Retention)-month(Cohort_Month),End_Retention,cohort_size  
from cte  
order by yy,mm  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our [documentation][1] to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--https://learn.microsoft.com/en-us/answers/questions/142427/users-always-get-connection-timeout-problem-when-u.html