网易大数据面试SQL-公会成员活跃度排名
⚠️ 待修正
一、题目背景
这道题来自网易的数据分析岗面试。网易是游戏和音乐，数据分析师需要从海量业务数据中挖掘洞见。
业务场景：用户粘性相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。
一、题目
现有一张公会成员活跃度表 t1_guild_activity，记录了各公会成员的在线时长和贡献值。请在每个公会内部按活跃度（在线时长 + 贡献值 * 10）对成员进行排名，要求并列的活跃度使用相同排名且排名连续（即 DENSE_RANK）。
公会活跃度表 t1_guild_activity：
+----------+-----------+-----------+-----------+
| guild_id | member_id | duration | contrib |
+----------+-----------+-----------+-----------+
| G01 | M001 | 120 | 500 |
| G01 | M002 | 200 | 300 |
| G01 | M003 | 150 | 450 |
| G01 | M004 | 150 | 500 |
| G02 | M005 | 100 | 600 |
| G02 | M006 | 180 | 400 |
| G02 | M007 | 180 | 400 |
| G02 | M008 | 90 | 200 |
+----------+-----------+-----------+-----------+
三、思路分析
本题考察窗口函数 DENSE_RANK() 在分组内按复合计算值排序的应用。核心公式：活跃度 = duration + contrib * 10。
解题步骤：
- 计算每个成员的活跃度分数
activity_score = duration + contrib * 10； - 使用
DENSE_RANK() OVER (PARTITION BY guild_id ORDER BY activity_score DESC)进行排名； - 最终结果按公会和排名排序展示。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |
四、逐步推导
1. 计算活跃度分数
执行SQL
select guild_id,
member_id,
duration,
contrib,
duration + contrib * 10 as activity_score
from t1_guild_activity
执行结果
+----------+-----------+----------+---------+----------------+
| guild_id | member_id | duration | contrib | activity_score |
+----------+-----------+----------+---------+----------------+
| G01 | M001 | 120 | 500 | 5120 |
| G01 | M002 | 200 | 300 | 3200 |
| G01 | M003 | 150 | 450 | 4650 |
| G01 | M004 | 150 | 500 | 5150 |
| G02 | M005 | 100 | 600 | 6100 |
| G02 | M006 | 180 | 400 | 4180 |
| G02 | M007 | 180 | 400 | 4180 |
| G02 | M008 | 90 | 200 | 2090 |
+----------+-----------+----------+---------+----------------+
2. 使用 DENSE_RANK 排名
执行SQL
select guild_id,
member_id,
duration,
contrib,
activity_score,
dense_rank() over (partition by guild_id order by activity_score desc) as rank
from (
select guild_id,
member_id,
duration,
contrib,
duration + contrib * 10 as activity_score
from t1_guild_activity
) t
order by guild_id, rank
执行结果
+----------+-----------+----------+---------+----------------+------+
| guild_id | member_id | duration | contrib | activity_score | rank |
+----------+-----------+----------+---------+----------------+------+
| G01 | M004 | 150 | 500 | 5150 | 1 |
| G01 | M001 | 120 | 500 | 5120 | 2 |
| G01 | M003 | 150 | 450 | 4650 | 3 |
| G01 | M002 | 200 | 300 | 3200 | 4 |
| G02 | M005 | 100 | 600 | 6100 | 1 |
| G02 | M006 | 180 | 400 | 4180 | 2 |
| G02 | M007 | 180 | 400 | 4180 | 2 |
| G02 | M008 | 90 | 200 | 2090 | 3 |
+----------+-----------+----------+---------+----------------+------+
五、常见坑点
坑1：同天多条记录干扰排序 — 如果同一天有多条记录，ROW_NUMBER() 分配不同行号导致差值法分组失效。先用 SELECT DISTINCT 去重。
坑2：date_sub 跨月自动处理 — date_sub 跨月时自动修正月份边界，但不要用字符串运算代替日期函数。
坑3：NULL值使窗口函数行为不一致 — ORDER BY 中 NULL 默认排到最后（Spark SQL: NULLS LAST），不指定时可能排序错乱。
六、举一反三
-
最大连续未登录/未出单天数：反向计算断签天数，识别即将流失的用户或商家
-
指定连续N天筛选：HAVING中加
COUNT(*) >= N，用于签到奖励或金牌商家认证 -
按维度分组的连续：PARTITION BY 加入 category/region，对比不同维度的连续行为差异
七、知识点总结
| 考点 | 说明 |
|---|---|
| RANK / DENSE_RANK / ROW_NUMBER | 排名函数三剑客，并列处理方式不同 |
| PARTITION BY | 窗口函数按列分组，实现组内独立计算 |
八、建表语句和数据插入
点击展开 DDL & DML
-- 建表语句
CREATE TABLE t1_guild_activity (
guild_id string COMMENT '公会ID',
member_id string COMMENT '成员ID',
duration int COMMENT '在线时长(小时)',
contrib int COMMENT '贡献值'
) COMMENT '公会成员活跃度表';
-- 数据插入
INSERT INTO t1_guild_activity VALUES
('G01', 'M001', 120, 500),
('G01', 'M002', 200, 300),
('G01', 'M003', 150, 450),
('G01', 'M004', 150, 500),
('G02', 'M005', 100, 600),
('G02', 'M006', 180, 400),
('G02', 'M007', 180, 400),
('G02', 'M008', 90, 200);
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
