网易大数据面试SQL-玩家付费ARPU和ARPPU计算
⚠️ 待修正
一、题目背景
这道题来自网易的数据分析岗面试。网易是游戏和音乐，数据分析师需要从海量业务数据中挖掘洞见。
业务场景：游戏运营相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。
一、题目
现有一张游戏玩家登录和付费记录表 t1_player_log，记录了每个玩家每天的登录状态和付费金额。请按日期计算：
- ARPU（每用户平均收入）= 总收入 / 活跃用户数
- ARPPU（每付费用户平均收入）= 总收入 / 付费用户数
玩家日志表 t1_player_log：
+-----------+------------+--------+------------+
| player_id | log_date | login | pay_amount |
+-----------+------------+--------+------------+
| P001 | 2024-01-01 | 1 | 50.00 |
| P002 | 2024-01-01 | 1 | 0.00 |
| P003 | 2024-01-01 | 1 | 128.00 |
| P004 | 2024-01-01 | 1 | 30.00 |
| P005 | 2024-01-01 | 0 | 0.00 |
| P001 | 2024-01-02 | 1 | 0.00 |
| P002 | 2024-01-02 | 1 | 88.00 |
| P003 | 2024-01-02 | 1 | 200.00 |
| P004 | 2024-01-02 | 0 | 0.00 |
| P006 | 2024-01-02 | 1 | 66.00 |
+-----------+------------+--------+------------+
三、思路分析
本题考察分组聚合和业务指标计算。ARPU 和 ARPPU 是游戏数据分析中最基础的两个指标。
解题步骤：
- 筛选登录用户（
login = 1）作为活跃用户； - 计算每日总收入
SUM(pay_amount)，活跃用户数COUNT(DISTINCT player_id)（login=1），付费用户数COUNT(DISTINCT player_id)（pay_amount > 0）； - 分别计算 ARPU 和 ARPPU。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
1. 按日统计活跃用户数、付费用户数和总收入
执行SQL
select log_date,
count(distinct case when login = 1 then player_id end) as dau,
count(distinct case when pay_amount > 0 then player_id end) as pay_users,
sum(pay_amount) as total_revenue
from t1_player_log
group by log_date
执行结果
+------------+------+-----------+---------------+
| log_date | dau | pay_users | total_revenue |
+------------+------+-----------+---------------+
| 2024-01-01 | 4 | 3 | 208.00 |
| 2024-01-02 | 4 | 3 | 354.00 |
+------------+------+-----------+---------------+
2. 计算 ARPU 和 ARPPU
执行SQL
select log_date,
dau,
pay_users,
total_revenue,
round(total_revenue / dau, 2) as arpu,
round(total_revenue / pay_users, 2) as arppu
from (
select log_date,
count(distinct case when login = 1 then player_id end) as dau,
count(distinct case when pay_amount > 0 then player_id end) as pay_users,
sum(pay_amount) as total_revenue
from t1_player_log
group by log_date
) t
执行结果
+------------+------+-----------+---------------+-------+--------+
| log_date | dau | pay_users | total_revenue | arpu | arppu |
+------------+------+-----------+---------------+-------+--------+
| 2024-01-01 | 4 | 3 | 208.00 | 52.00 | 69.33 |
| 2024-01-02 | 4 | 3 | 354.00 | 88.50 | 118.00 |
+------------+------+-----------+---------------+-------+--------+
五、常见坑点
坑1：NULL值的隐式处理 — 聚合函数跳过NULL，窗口函数不跳过。WHERE需显式过滤或用COALESCE替代。
坑2：数据类型不一致导致JOIN失效 — string vs int隐式转换可能触发全表扫描，性能骤降。
六、举一反三
-
增加时间维度对比：按天/周/月分组，观察指标的趋势和季节性波动
-
增加分组维度：按品类/地区/渠道拆解，发现差异化的业务洞察
-
设置预警阈值：在WHERE中加阈值判断，自动筛选异常数据
七、知识点总结
| 考点 | 说明 |
|---|---|
| CASE WHEN 条件聚合 | 灵活实现分段统计、条件计数、标签化处理 |
| GROUP BY + 聚合函数 | 分组聚合是数据分析的基础，配合HAVING筛选分组结果 |
| 日期函数 | DATEDIFF / DATE_ADD / unix_timestamp处理日期运算 |
八、建表语句和数据插入
点击展开 DDL & DML
-- 建表语句
CREATE TABLE t1_player_log (
player_id string COMMENT '玩家ID',
log_date string COMMENT '日期',
login int COMMENT '是否登录(1是0否)',
pay_amount double COMMENT '付费金额'
) COMMENT '玩家登录付费日志表';
-- 数据插入
INSERT INTO t1_player_log VALUES
('P001', '2024-01-01', 1, 50.00),
('P002', '2024-01-01', 1, 0.00),
('P003', '2024-01-01', 1, 128.00),
('P004', '2024-01-01', 1, 30.00),
('P005', '2024-01-01', 0, 0.00),
('P001', '2024-01-02', 1, 0.00),
('P002', '2024-01-02', 1, 88.00),
('P003', '2024-01-02', 1, 200.00),
('P004', '2024-01-02', 0, 0.00),
('P006', '2024-01-02', 1, 66.00);
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
