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

# 创建recursive递归查询递归遍历
with recursive tab as(
# 连接双表并限定新会员入会日期作为基础查询表
select rr.referrer_id,m.member_name,rr.referee_id,rr.join_date,rr.base_reward_points
from referral_records as rr
left join members as m on rr.referrer_id = m.member_id
where rr.join_date between '2025-01-01' and '2025-06-30'
), query as(
# 复用tab基础表，创立第一个层级1,同时基础查询表限定张三为初始推荐人
select referrer_id,member_name,referee_id,join_date,base_reward_points,1 as level
from tab
where member_name = '张三'

union all
# 递归合并查询结果，每次层级+1
select tab.referrer_id,tab.member_name,tab.referee_id,tab.join_Date,tab.base_reward_points,query.level + 1 as level
from tab join query on tab.referrer_id = query.referee_id
)
# 筛选出row_number标记为1的最高层级，并计算实际积分，最后调整排序
select referee_id,referee_name,level as referral_level,round(base_reward_points*pow(0.5,level-1),2) as actual_points
from
(
# 结果包含重复，用row_number筛选出层级最大的id
SELECT q.referrer_id,q.member_name,q.referee_id,m.member_name as referee_name,q.base_reward_points,q.level,row_number() over(partition by q.referee_id order by q.level desc) as highest_level
FROM query as q
left join members as m
on q.referee_id = m.member_id
) as temp
where highest_level <= 1
order by level asc,actual_points desc,referee_id asc