Add variable to count consecutive months

Question

I have a query in a Postgres DB that combines clients subscriptions.

I want to add a variable that's called "consecutive months" but I'm not sure how to do it in Postgres.

My raw table is something like this:

client	product	Date
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

and I inteed to have something that counts the consecutive months originating something like this:

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

Thank you for the help in advanced!

Answer 1

Looks like you got yourself a Gaps-And-Islands type of problem.

The trick is to calculate some ranking based on connected dates per client.

Then a sequencial number can be calculated based on client and the rank.

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

db<>fiddle here