By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE client_contracts
(
id bigint NOT NULL,
client_id bigint NOT NULL,
name character varying COLLATE pg_catalog."default",
autopay_status character varying COLLATE pg_catalog."default",
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) VALUES (1,1,'Contract 1','Inactive',1111,'2019-08-30','2020-02-29');
INSERT INTO client_contracts(id, client_id, name, autopay_status, contract_id, start_date, end_date) VALUES (2,1,'Contract 1','Inactive',1112,'2020-02-29','2020-08-29');
INSERT INTO client_contracts(id, client_id, name, autopay_status, contract_id, start_date, end_date) VALUES (3,1,'Contract 1','Active',1113,'2020-08-29','2021-02-28');
INSERT INTO client_contracts(id, client_id, name, autopay_status, contract_id, start_date, end_date) VALUES (4,2,'Contract 2','Inactive',2221,'2019-08-30','2020-02-29');
INSERT INTO client_contracts(id, client_id, name, autopay_status, contract_id, start_date, end_date) VALUES (5,2,'Contract 2','Inactive',2222,'2020-02-29','2020-08-29');
INSERT INTO client_contracts(id, client_id, name, autopay_status, contract_id, start_date, end_date) VALUES (6,2,'Contract 2','Active',2223,'2020-08-29','2021-02-28');
INSERT INTO client_contracts(id, client_id, name, autopay_status, contract_id, start_date, end_date) VALUES (7,3,'Contract 3','Inactive',3331,'2019-08-30','2020-02-29');
INSERT INTO client_contracts(id, client_id, name, autopay_status, contract_id, start_date, end_date) VALUES (8,3,'Contract 3','Inactive',3332,'2020-03-29','2020-09-29');
INSERT INTO client_contracts(id, client_id, name, autopay_status, contract_id, start_date, end_date) VALUES (9,3,'Contract 3','Inactive',3333,'2020-09-29','2021-03-28');
INSERT INTO client_contracts(id, client_id, name, autopay_status, contract_id, start_date, end_date) VALUES (10,3,'Contract 3','Inactive',3334,'2021-04-28','2021-10-28');
INSERT INTO client_contracts(id, client_id, name, autopay_status, contract_id, start_date, end_date) VALUES (11,3,'Contract 3','Inactive',3335,'2021-10-28','2022-04-28');
INSERT INTO client_contracts(id, client_id, name, autopay_status, contract_id, start_date, end_date) VALUES (12,3,'Contract 3','Active',3336,'2022-04-28','2022-10-28');
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
select version();
| version |
|---|
| PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit |
SELECT
case when
(start_date - coalesce(lag(end_date) over (partition by client_id order by end_date), end_date)::date)::int <= 1 then true
else false
end as continous_contract,
end_date - start_date as contract_days,
client_id,
contract_id,
autopay_status,
start_date,
end_date
FROM
client_contracts
ORDER BY
client_id, start_date
| continous_contract | contract_days | client_id | contract_id | autopay_status | start_date | end_date |
|---|---|---|---|---|---|---|
| t | 183 | 1 | 1111 | Inactive | 2019-08-30 | 2020-02-29 |
| t | 182 | 1 | 1112 | Inactive | 2020-02-29 | 2020-08-29 |
| t | 183 | 1 | 1113 | Active | 2020-08-29 | 2021-02-28 |
| t | 183 | 2 | 2221 | Inactive | 2019-08-30 | 2020-02-29 |
| t | 182 | 2 | 2222 | Inactive | 2020-02-29 | 2020-08-29 |
| t | 183 | 2 | 2223 | Active | 2020-08-29 | 2021-02-28 |
| t | 183 | 3 | 3331 | Inactive | 2019-08-30 | 2020-02-29 |
| f | 184 | 3 | 3332 | Inactive | 2020-03-29 | 2020-09-29 |
| t | 180 | 3 | 3333 | Inactive | 2020-09-29 | 2021-03-28 |
| f | 183 | 3 | 3334 | Inactive | 2021-04-28 | 2021-10-28 |
| t | 182 | 3 | 3335 | Inactive | 2021-10-28 | 2022-04-28 |
| t | 183 | 3 | 3336 | Active | 2022-04-28 | 2022-10-28 |