【阿里巴巴】使用递归 CTE 统计会员推荐网络积分
JOIN 连接路径分析面试真题
题目描述
来源：阿里 SQL 面试真题
某大型连锁健身房在 2025 年上半年推出“老带新”裂变营销活动。现有会员可以推荐新会员入会，新会员入会后也可以继续推荐其他人。直接推荐人可以获得基础奖励积分，间接推荐人的积分会随着推荐层级加深而衰减。
店长希望追溯名为“张三”的活跃会员在活动期间建立的完整推荐网络，并核算其网络中每个节点的实际贡献积分。
数据表
members 会员表
| 字段 | 类型 | 说明 |
|---|---|---|
| member_id | INT | 会员编号，主键 |
| member_name | VARCHAR(64) | 会员姓名 |
| membership_type | VARCHAR(32) | 会员卡类型 |
referral_records 推荐记录表
| 字段 | 类型 | 说明 |
|---|---|---|
| referrer_id | INT | 推荐人编号 |
| referee_id | INT | 被推荐人编号 |
| join_date | DATE | 新会员正式签约入会日期 |
| base_reward_points | FLOAT | 本次推荐产生的基础奖励积分 |
业务规则
- 只统计 join_date 在 2025-01-01 至 2025-06-30 之间的推荐关系，包含首尾两日。
- 递归网络中的每一条推荐关系都必须处于这个时间范围内；如果某条下游推荐关系超出范围，则该关系及其下游分支都不进入结果。
- 由“张三”直接推荐的会员层级为 1，继续向下每递归一层，层级加 1。
- 实际贡献积分 = base_reward_points × 0.5^(推荐层级 - 1)。
- actual_points 四舍五入保留 2 位小数。
输出要求
查询由“张三”直接或间接推荐入会的所有下线会员，输出：
| 字段 | 说明 |
|---|---|
| referee_id | 被推荐人编号 |
| referee_name | 被推荐人姓名 |
| referral_level | 推荐层级，直接推荐为 1 |
| actual_points | 实际贡献积分，四舍五入保留 2 位 |
排序规则：
- referral_level 升序。
- actual_points 降序。
- referee_id 升序。
数据样例
| member_idPKINT | member_nameVARCHAR(64) | membership_typeVARCHAR(32) |
|---|---|---|
| 1 | 张三 | 年卡 |
| 2 | 李四 | 私教包月卡 |
| 3 | 王五 | 年卡 |
| 4 | 赵六 | 季卡 |
| 5 | 孙七 | 年卡 |
| 6 | 周八 | 私教包月卡 |
| 7 | 吴九 | 季卡 |
| 8 | 郑十 | 年卡 |
输入数据显示 8 / 10 行
SQL 编辑器正在保存草稿...
Ctrl + Enter 运行
正在加载 SQL 编辑器...
运行你的 SQL 查询后，结果将显示在这里