PostgreSQL中拆分Premium订阅期适配Books订阅的技术求助

数据表说明 #

• 表名：subscriptions（已激活订阅数据表）
• 字段：
  • User ID：用户ID
  • Start Date of Subscription：订阅开始日期
  • End Date of Subscription：订阅结束日期（当前处于激活状态的订阅，结束日期设为当日）
• 订阅类型：Premium、Books，二者可独立激活

示例数据 #

需求说明 #

当Books订阅在Premium订阅有效期内激活时，需将Premium订阅拆分为Books订阅开始前、Books订阅结束后的多个时段，期望输出如下：

现有代码问题 #

我编写了如下SQL代码，但仅能将Premium订阅拆分为Books订阅开始前的时段，不知道如何实现Books订阅结束后继续Premium订阅的逻辑：

WITH ordered_subscriptions AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY start_date) as rn
    FROM subscriptions
),
date_ranges AS (
    SELECT 
        a.user_id, 
        a.start_date, 
        MIN(b.start_date) as end_date,
        a.subscription_type
    FROM ordered_subscriptions a
    LEFT JOIN ordered_subscriptions b ON a.user_id = b.user_id AND a.rn < b.rn
    GROUP BY a.user_id, a.start_date, a.subscription_type
),
filtered_subscriptions AS (
    SELECT 
        user_id, 
        start_date, 
        COALESCE(end_date, CURRENT_DATE) as end_date, 
        subscription_type
    FROM date_ranges
    WHERE subscription_type = 'Premium' AND NOT EXISTS (
        SELECT 1 
        FROM date_ranges d2 
        WHERE d2.user_id = date_ranges.user_id 
        AND d2.subscription_type = 'Books' 
        AND d2.start_date < date_ranges.end_date 
        AND (d2.end_date IS NULL OR d2.end_date > date_ranges.start_date)
    )
)

SELECT * FROM filtered_subscriptions
UNION ALL
SELECT user_id, start_date, COALESCE(end_date, CURRENT_DATE), subscription_type 
FROM date_ranges 
WHERE subscription_type = 'Books'
ORDER BY user_id, start_date;

内容的提问来源于stack exchange，提问作者Кирилл