假设您希望保留所有的 Books 订阅。如果有一个 Premium 订阅以某种方式重叠，则重叠的期间需要被吸收到另一个订阅中，同时调整/创建非重叠的（可能是两个）。
两个这样的订阅可以有四种不同的交叉方式。还有一种可能性是，多个 Books 可以归入一个 Premium。查询的第一部分确定是否存在重叠，重叠的类型，并收集与这种链中的前向和后向链接相关的数据。
第二部分生成一组四个日期，对应于重叠的不同部分之间的边界。它还处理创建一个额外的行，其中 Premium 必须分为三部分（一部分保留为 Books），并且 Premium 完全包含在 Books 中，并且需要完全消失。
其余的逻辑只是驱动所有部件的组装。注意不要在“链接”发生的地方复制片段。
这里没有递归，只有一个连接，所以我认为这将是有效的。在列user_id, start_date, end_date上建立适当的索引可能会有好处。

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;

字符串
https://dbfiddle.uk/chT-SLiC
我原本认为使用外部连接会比使用联合更干净，生成更好的计划。潜在地，即使两个查询大部分相同，实际上更好地走另一条路。
https://dbfiddle.uk/raMy-wOL的

**UPDATE：**我更喜欢这个版本的查询。这是一个稍微不同的方法，它可以处理一些其他方法不能处理的情况。我会尝试将这些合并在一起，但现在我会忽略上面的查询。

with step1 as (
    select
        s.user_id,
        s.start_date, s.end_date,
        b.start_date as b_start_date, b.end_date as b_end_date,
        min(b.start_date)  over (partition by s.user_id, s.start_date order by b.start_date) as firstb_date,
        lag(b.end_date)    over (partition by s.user_id, s.start_date order by b.start_date) as priorb_date,
        lead(b.start_date) over (partition by s.user_id, s.start_date order by b.start_date) as nextb_date,
        max(b.end_date)    over (partition by s.user_id, s.start_date) as lastb_date
    from ordered_subscriptions s left outer join ordered_subscriptions as b
        on      s.user_id = b.user_id and b.subscription_type = 'Books'
            and s.start_date <= b.end_date and s.end_date >= b.start_date
    where s.subscription_type = 'Premium'
), step2 as (
    select *,
        case when start_date  < firstb_date then '<'
             when start_date >= firstb_date then '>'
                  /* else null */           else '*' end as l_overlap,
        case when end_date   <= lastb_date  then '<'
             when end_date    > lastb_date  then '>'
                  /* else null */           else '*' end as r_overlap
    from step1
)
select user_id, 'Premium' as subscription_type,
    case l_overlap when '*' then start_date
                   when '<' then case split when '+' then b_end_date
                            else coalesce(priorb_date, start_date) end
                   when '>' then b_end_date end as start_date,
    case l_overlap when '*' then end_date
                   when '<' then case split when '+' then end_date
                            else b_start_date end
                   when '>' then coalesce(nextb_date, end_date) end as end_date  
from step2 left outer join lateral ( 
        select * from (values ('='), ('+')) as v(split)
        where l_overlap || r_overlap = '<>' and nextb_date is null
    ) as split on 1 = 1
where l_overlap || r_overlap <> '><'
union all
select user_id, subscription_type, start_date, end_date
from ordered_subscriptions where subscription_type = 'Books'
order by user_id, start_date, end_date;

型
https://dbfiddle.uk/Y2T_q-kV的

请参阅示例。
subscription_type为Premium的期间与行Books（更准确地说，不是Premium）联接在一起。

Premium period     =====++++========++++++++====
Books period 1  --------|  |        |      |
Books period 2             |        |      |---------
Books period 1 and 2       |--------|

字符串
为了减少计算量，我们还立即取上一个Books周期的结束和下一个周期的开始。“
如果帐簿期间与保险费期间完全重叠，则条件1和2将不满足，该行将从输出中排除（内部联接）。
对于联合序列，如2023-12-01->2023 - 12-04 2023 <->-12-04->2023-12-16，需要澄清条件。

with tB as(
select *
   ,lag(end_date)over(partition by user_id order by start_date) prev_end
   ,lead(start_date)over(partition by user_id order by start_date) next_start
from subscriptions s
where s.subscription_type<>'Premium'
)
select user_id,start_daten start_date,end_daten end_date,subscription_type
  --,start_dateorig,end_dateorig,start_dateb,end_dateb
from(
select s.user_id,s.subscription_type
  ,s.start_date as start_dateOrig,s.end_date as end_dateOrig
  ,tb.start_date as start_dateB,tb.end_date as end_dateB
  ,case when n=1 then tb.end_date
        when n=2 then 
         case when tb.prev_end is null or tb.prev_end<s.start_date then s.start_date
         else tb.prev_end
         end
   end start_dateN
  ,case when n=2 then tb.start_date
        when n=1 then 
         case when tb.next_start is null then s.end_date --? current_date 
              when tb.next_start>s.end_date then s.end_date
         else tb.next_start
         end
   end end_dateN
from subscriptions s
left join tb on tB.user_id=s.user_id 
   and ( tb.start_date between s.start_date and s.end_date
       or tb.end_date between s.start_date and s.end_date) 
inner join (select 1 n union all select 2 ) nn 
  on   (n=1 and tb.end_date between s.start_date and s.end_date )
    or (n=2 and tb.start_date between s.start_date and s.end_date 
          and (tb.prev_end is null or tb.prev_end<s.start_date) )
where s.subscription_type='Premium'
) x
union all
select user_id,start_date,end_date
  ,subscription_type
from tB s
where s.subscription_type<>'Premium'
union all -- subscriptions without intersections
select user_id,start_date,end_date
  ,subscription_type
from subscriptions s
where s.subscription_type='Premium' 
      and not exists (select 1 from subscriptions s2 
        where s2.subscription_type<>'Premium'
           and (s2.start_date between s.start_date and s.end_date
             or s2.end_date between s.start_date and s.end_date)
         )  
order by user_id,start_date

型
Fiddle here