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?.
select version();
| version |
|---|
| PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit |
create table cust
(
customerid int,
status varchar(10),
dt date
);
insert into cust values(1001,'Active','2021-01-01');
insert into cust values(1001,'Active','2021-03-01');
insert into cust values(1001,'Inactive','2021-01-30');
insert into cust values(1001,'Inactive','2021-04-01');
insert into cust values(1001,'Active','2021-09-01');
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
WITH active_status
AS (SELECT customerid,
Row_number()
OVER (
partition BY customerid
ORDER BY start_date) id,
start_date
FROM (SELECT customerid,
CASE
WHEN status = 'Active' THEN dt
END "start_date"
FROM cust) x
WHERE start_date IS NOT NULL),
inactive_status
AS (SELECT customerid,
Row_number()
OVER (
partition BY customerid
ORDER BY end_date) id,
end_date
FROM (SELECT customerid,
CASE
WHEN status = 'Inactive' THEN dt
END "end_date"
FROM cust) x
WHERE end_date IS NOT NULL)
SELECT acta.customerid,
acta.start_date,
COALESCE(inacta.end_date, '2099-12-31') end_date
FROM active_status acta
LEFT OUTER JOIN inactive_status inacta
ON acta.customerid = inacta.customerid
AND acta.id = inacta.id
ORDER BY start_date;
| customerid | start_date | end_date |
|---|---|---|
| 1001 | 2021-01-01 | 2021-01-30 |
| 1001 | 2021-03-01 | 2021-04-01 |
| 1001 | 2021-09-01 | 2099-12-31 |