id bigint PRIMARY KEY

, client_id bigint NOT NULL

, name varchar

, autopay_status varchar

, contract_id integer

, start_date date

, end_date date

);

INSERT INTO client_contracts(id, client_id, name, autopay_status, contract_id, start_date, end_date)

( 1,1,'Contract 1','Inactive',1111,'2019-08-30','2020-02-29')

, ( 2,1,'Contract 1','Inactive',1112,'2020-02-29','2020-08-29')

, ( 3,1,'Contract 1','Active' ,1113,'2020-08-29','2021-02-28')

, ( 4,2,'Contract 2','Inactive',2221,'2019-08-30','2020-02-29')

, ( 5,2,'Contract 2','Inactive',2222,'2020-02-29','2020-08-29')

, ( 6,2,'Contract 2','Active' ,2223,'2020-08-29','2021-02-28')

, ( 7,3,'Contract 3','Inactive',3331,'2019-08-30','2020-02-29')

, ( 8,3,'Contract 3','Inactive',3332,'2020-03-29','2020-09-29')

, ( 9,3,'Contract 3','Inactive',3333,'2020-09-29','2021-03-28')

, (10,3,'Contract 3','Inactive',3334,'2021-04-28','2021-10-28')

, (11,3,'Contract 3','Inactive',3335,'2021-10-28','2022-04-28')

, (12,3,'Contract 3','Active' ,3336,'2022-04-28','2022-10-28')

;

FROM (

SELECT *, count(*) FILTER (WHERE NOT continous_contract) OVER (PARTITION BY client_id ORDER BY end_date) AS contract_nr

FROM (

SELECT client_id, start_date, end_date

, start_date <= lag(end_date, 1, end_date) OVER (PARTITION BY client_id ORDER BY end_date) + 1 AS continous_contract

, end_date - start_date AS contract_days -- + 1 ???

FROM client_contracts

) sub1

) sub2

ORDER BY client_id, start_date;