题解 | 全民健身季推荐网络与积分衰减计算

with recursive cte as (
    select
        r.referee_id,
        m1.member_name,
        1 as depth,
        r.base_reward_points * pow(0.5,0) as actual_points
    from
        members m
    join
        referral_records r on m.member_id = r.referrer_id
    join
        members m1 on r.referee_id = m1.member_id
    where
        m.member_name = '张三' and r.join_date >= '2025-01-01' and r.join_date <'2025-07-01'
    union all

    select
        r.referee_id,
        m.member_name,
        c.depth+1 as depth,
        r.base_reward_points * pow(0.5,c.depth) as actual_points
    from
        cte c
    join
        referral_records r on c.referee_id = r.referrer_id
    join
         members m on m.member_id = r.referee_id
    where
        r.join_date >= '2025-01-01' and r.join_date <'2025-07-01'
)

select
    referee_id ,
    member_name as referee_name,
    depth as referral_level,
    actual_points
from
    cte
order by
    referral_level,actual_points desc,referee_id