Postgres 10

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 ordered_subscriptions (
  user_id integer,
  start_date date,
  end_date date,
  subscription_type varchar(7)
);

INSERT INTO ordered_subscriptions
  (user_id, start_date, end_date, subscription_type)
VALUES
  (675, '2023-01-01', '2023-05-10', 'Premium'),
  (675, '2023-02-15', '2023-02-28', 'Books'),
  (675, '2023-04-18', '2023-06-18', 'Books'),
  (677, '2022-01-01', '2022-12-31', 'Premium'),
  (677, '2023-01-01', '2023-07-10', 'Premium'),
  (677, '2023-02-15', '2023-02-28', 'Books'),
  (677, '2023-04-18', '2023-06-18', 'Books'),
  (699, '2023-01-01', '2023-07-10', 'Premium'),
  (699, '2023-02-15', '2023-02-28', 'Books'),
  (699, '2023-04-18', '2023-06-18', 'Books'),
  (699, '2023-06-20', '2023-06-22', 'Books'),
  (726, '2023-01-01', '2023-10-10', 'Premium'),
  (726, '2023-03-16', '2023-05-28', 'Books'),
  (855, '2023-04-05', '2023-05-28', 'Books'),
  (855, '2023-04-20', '2023-07-25', 'Premium'),
  (900, '2023-07-01', '2023-07-10', 'Premium'),
  (900, '2023-01-01', '2023-12-31', 'Books');

CREATE TABLE

INSERT 0 17

with data as (
    select
        s.user_id, s.subscription_type, s.start_date, s.end_date,
        b.start_date as b_start_date, b.end_date as b_end_date,
        case when b.subscription_type is not null
             then lag(b.end_date)
                      over (partition by s.user_id, s.start_date order by b.start_date) end as pb_end_date,
        case when b.subscription_type is not null
             then lead(b.start_date)
                      over (partition by s.user_id, s.start_date order by b.start_date) end as nb_start_date,
        case when s.start_date < b.start_date then 'p'
             when s.start_date > b.start_date then 'b' else '' end ||
        case when s.end_date   < b.end_date   then 'b'
             when s.end_date   > b.end_date   then 'p' else ''end as overlap
    from ordered_subscriptions s left outer join ordered_subscriptions as b
        on      s.subscription_type = 'Premium' and b.subscription_type = 'Books'
            and s.user_id = b.user_id
            and s.start_date < b.end_date and s.end_date > b.start_date
), data2 as (
    select
        user_id, subscription_type, overlap,
        case when pb_end_date is not null then 'Y' else 'N' end as linked,
        least(start_date, b_start_date) as d1,
        greatest(start_date, b_start_date) as d2,
        least(end_date, b_end_date) as d3,
        coalesce(nb_start_date, greatest(end_date, b_end_date)) as d4,
        seg_num
    from data left outer join lateral (
            select * from (values (1), (2)) as v(seg_num)
            where subscription_type = 'Premium' and (seg_num = 1 or overlap = 'pp')
        ) as s(seg_num) on 1 = 1
    where (subscription_type = 'Books' or overlap <> 'bb')
)
select
    user_id, subscription_type,
    case when seg_num is null then d1
         when seg_num = 1 then case when overlap = 'bp' then d3 else d1 end
         when seg_num = 2 then d3 end as start_date,
    case when seg_num is null then d3
         when seg_num = 1 then case when overlap = 'bp' then d4 else d2 end
         when seg_num = 2 then d4 end as end_date
from data2
where (linked = 'N' or seg_num = 2)
order by user_id, start_date, end_date

user_id	subscription_type	start_date	end_date
675	Premium	2023-01-01	2023-02-15
675	Books	2023-02-15	2023-02-28
675	Premium	2023-02-28	2023-04-18
675	Books	2023-04-18	2023-06-18
677	Premium	2022-01-01	2022-01-01
677	Premium	2023-01-01	2023-02-15
677	Books	2023-02-15	2023-02-28
677	Premium	2023-02-28	2023-04-18
677	Books	2023-04-18	2023-06-18
677	Premium	2023-06-18	2023-07-10
699	Premium	2023-01-01	2023-02-15
699	Books	2023-02-15	2023-02-28
699	Premium	2023-02-28	2023-04-18
699	Books	2023-04-18	2023-06-18
699	Premium	2023-06-18	2023-06-20
699	Books	2023-06-20	2023-06-22
699	Premium	2023-06-22	2023-07-10
726	Premium	2023-01-01	2023-03-16
726	Books	2023-03-16	2023-05-28
726	Premium	2023-05-28	2023-10-10
855	Books	2023-04-05	2023-05-28
855	Premium	2023-05-28	2023-07-25
900	Books	2023-01-01	2023-12-31

SELECT 23