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

with recursive temp as(
    select 
        m1.member_id,
        m1.member_name,
        1 as referral_level,
        rr.base_reward_points
    from referral_records rr
        join members m1 on rr.referee_id=m1.member_id
        join members m2 on rr.referrer_id=m2.member_id
    where m2.member_name='张三' 
        and join_date between '2025-01-01' and '2025-06-30'

    union all 

    select 
        m1.member_id,
        m1.member_name,
        1+referral_level,
        rr.base_reward_points
    from temp t
        join referral_records rr on rr.referrer_id=t.member_id
        join members m1 on rr.referee_id=m1.member_id
    where join_date between '2025-01-01' and '2025-06-30'
)
select 
    member_id as referee_id,
    member_name as referee_name,
    referral_level,
    round(base_reward_points*pow(0.5,referral_level-1),2) as actual_points
from temp
order by referral_level,actual_points desc,referee_id;