Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table raw (
id bigint GENERATED BY DEFAULT AS IDENTITY primary key, 
client int, 
product varchar(30),
"Date" date
)

CREATE TABLE


insert into raw (client, product, "Date") values
(1, 'Sub', '2020-10-01'), 
(1, 'Sub', '2020-11-01'), 
(2, 'Sub', '2020-11-01'), 
(2, 'Sub', '2020-12-01'), 
(1, 'Sub', '2021-01-01'), 
(1, 'Sub', '2021-02-01'), 
(2, 'Sub', '2021-02-01');

INSERT 0 7


select client, product, "Date"
, row_number() over (partition by client, daterank order by "Date") as Consecutive_months
from
(
  select "Date", client, product
  , dense_rank() over (partition by client order by "Date") 
    + (DATE_PART('year', AGE(current_date, "Date"))*12 + 
       DATE_PART('month', AGE(current_date, "Date"))) daterank
from raw t
) q
order by "Date", client


client
product
Date
consecutive_months


1
Sub
2020-10-01
1

1
Sub
2020-11-01
2

2
Sub
2020-11-01
1

2
Sub
2020-12-01
2

1
Sub
2021-01-01
1

1
Sub
2021-02-01
2

2
Sub
2021-02-01
1



SELECT 7

client	product	Date	consecutive_months
1	Sub	2020-10-01	1
1	Sub	2020-11-01	2
2	Sub	2020-11-01	1
2	Sub	2020-12-01	2
1	Sub	2021-01-01	1
1	Sub	2021-02-01	2
2	Sub	2021-02-01	1