基于静态会员表生成时间序列，排查会员重叠周期问题

我有一张members表，记录了用户、会员类型、会员变更类型（A为新增，D为删除）及变更日期，数据如下：

我需要找出同一用户拥有多个会员且会员周期存在重叠的情况，当前使用以下SQL查询时返回了错误的结束日期：

With membership As

    (Select user, membership, date as Start_Date, 
    LEAD (date, 1, '31/12/9999') OVER (PARTITION BY membership ORDER BY 
    date) AS End_Date

    FROM
        (Select *, LAG(change,1,-1) OVER(PARTITION BY membership ORDER BY 
        date) AS Previous_change
        From members) withprevious
        Where change != previous_change),
    MemberTimeSeries AS
    (Select *
    From membership
    Where Start_Date IN
        (Select a.Date
        From members a
        join membership b
        on a.user = b.user
        and a.membership = b.membership
        Where a.change = 'A')),
    DupeIDs AS
    (Select Distinct a.user, a.membership, cast(a.start_date as date_ as 
    start_date, cast(a.end_date as date) as end_date
        from membertimeseries a
        join membertimeseries b
        on a.user = b.user
        and ((a.start_date >= b.start_date abd a.start_date < b.end_date)
        Or (a.end_date > b.start_date and a.end_date <= b.end_date)
        OR (b.start_date >= a.start_date abd b.start_date < a.end_date)
        Or (b.end_date > a.start_date and b.end_date <= a.end_date)

需求与期望输出 #

我需要获取所有存在会员周期重叠的用户-会员组合及其对应的生效起止日期，若表中无该会员的删除记录，则结束日期默认设为31/12/9999。期望输出如下：

内容的提问来源于stack exchange，提问作者Maitiu