My data is structured as below -
1.For each ID month denotes reporting month, Sub created is the original subscription purchase date, status = whether customer was active or not, tenure is lifetime months ( It resets to 1 upon the customer returning )
ID Month Sub_created status tenure
100 2017-02-01 2017-02-01 active 1
100 2017-03-01 active 2
100 2017-04-01 active 3
100 2017-05-01 churned 3
100 2021-02-01 2021-02-01 active 1
100 2021-03-01 active 2
100 2021-04-01 active 3
100 2021-05-01 active 4
100 2021-06-01 active 5
100 2021-07-01 active 6
I want to be able to have sub created date for all the rows till it has a new subscription date. The output I am trying to get is below -
ID Month Sub_created status tenure
100 2017-02-01 2017-02-01 active 1
100 2017-03-01 2017-02-01 active 2
100 2017-04-01 2017-02-01 active 3
100 2017-05-01 2017-02-01 churned 3
100 2021-02-01 2021-02-01 active 1
100 2021-03-01 2021-02-01 active 2
100 2021-04-01 2021-02-01 active 3
100 2021-05-01 2021-02-01 active 4
100 2021-06-01 2021-02-01 active 5
100 2021-07-01 2021-02-01 active 6
Can anyone suggest SQL code ? Thanks