SQL 游戏ARPU和ARPPU计算：总收入/用户数 vs 总收入/付费用户数（网易面试题）

一、题目背景

这道题来自网易游戏事业部的数据分析岗面试。ARPU（每用户平均收入）和 ARPPU（每付费用户平均收入）是游戏运营最核心的两个变现指标——ARPU 衡量整体用户的变现效率，ARPPU 衡量付费用户的消费深度。两者对比分析，可以快速定位"是付费率不足还是付费深度不够"。

业务场景：游戏运营每天出具"日度营收简报"，ARPU 和 ARPPU 是必看指标。如果 ARPU 下降但 ARPPU 稳定，说明新增活跃用户没转化为付费用户；如果 ARPPU 也在降，则需要策划新的付费活动拉动消费。

二、题目

现有一张游戏玩家登录和付费记录表 t1_player_log，记录了每个玩家每天的登录状态和付费金额。请按日期计算：

• ARPU（每用户平均收入）= 总收入 / 活跃用户数
• ARPPU（每付费用户平均收入）= 总收入 / 付费用户数

玩家日志表 t1_player_log：

+------------+-------------+--------+-------------+
| player_id  |  log_date   | login  | pay_amount  |
+------------+-------------+--------+-------------+
| P001       | 2024-01-01  | 1      | 50.0        |
| P002       | 2024-01-01  | 1      | 0.0         |
| P003       | 2024-01-01  | 1      | 128.0       |
| P004       | 2024-01-01  | 1      | 30.0        |
| P005       | 2024-01-01  | 0      | 0.0         |
| P001       | 2024-01-02  | 1      | 0.0         |
| P002       | 2024-01-02  | 1      | 88.0        |
| P003       | 2024-01-02  | 1      | 200.0       |
| P004       | 2024-01-02  | 0      | 0.0         |
| P006       | 2024-01-02  | 1      | 66.0        |
+------------+-------------+--------+-------------+

三、思路分析

本题考察分组聚合和业务指标计算。ARPU 和 ARPPU 是游戏数据分析中最基础的两个指标。

解题步骤：

1. 筛选登录用户（login = 1）作为活跃用户；
2. 计算每日总收入 SUM(pay_amount)，活跃用户数 COUNT(DISTINCT player_id)（login=1），付费用户数 COUNT(DISTINCT player_id)（pay_amount > 0）；
3. 分别计算 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-02  | 4    | 3          | 354.0          |
| 2024-01-01  | 4    | 3          | 208.0          |
+-------------+------+------------+----------------+
2 rows selected (1.476 seconds)(https://www.dwsql.com)

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-02  | 4    | 3          | 354.0          | 88.5  | 118.0  |
| 2024-01-01  | 4    | 3          | 208.0          | 52.0  | 69.33  |
+-------------+------+------------+----------------+-------+--------+
2 rows selected (0.59 seconds)(https://www.dwsql.com)

五、常见坑点

坑1：活跃用户和付费用户的统计口径不同

dau 统计 login = 1 的用户，pay_users 统计 pay_amount > 0 的用户。注意：有些玩家直接充值但不登录游戏（通过官网充值页面），这类用户会出现在付费用户中但不在活跃用户中。面试时应和面试官确认口径。

坑2：除数为零导致报错

如果某天没有任何活跃用户（dau = 0），total_revenue / dau 会直接报除零错误。解决方案：total_revenue / NULLIF(dau, 0)，此时 ARPU 返回 NULL 而非报错，业务侧用 COALESCE 填充为 0。

坑3：ARPU 和 ARPPU 分母混淆

ARPU 分母是活跃用户数（login=1），ARPPU 分母是付费用户数（pay_amount>0）。面试中常见错误是把 ARPU 的分母也写成 pay_amount > 0，导致两个指标完全一样，失去对比分析的意义。

六、举一反三

1. 付费渗透率：新增 ROUND(pay_users / dau, 4) 指标，表示活跃用户中有多少比例会付费。配合 ARPPU 一起看——渗透率上升说明"更多人付费"，ARPPU 上升说明"每人付更多"

2. 按渠道/版本拆解：GROUP BY 加入 channel / app_version，对比不同渠道用户的 ARPU 差异，定位"哪个渠道来的用户质量差"（高活跃低付费）

3. 分层 ARPPU：按付费金额分为小R(0-100)、中R(100-500)、大R(500+)三档，分别计算各档人数和人均付费，判断付费结构是否健康——大R占比过高意味着收入不稳定

4. 日环比/周同比：对 ARPU 做 LAG 环比，看日度波动是否在正常范围内，异常波动自动告警

七、知识点总结

考点	说明
CASE WHEN 条件聚合	CASE WHEN login = 1 THEN player_id END 对不同状态分别去重计数
COUNT DISTINCT	一个玩家一天可能有多条记录，必须去重统计独立用户
除法指标计算	ROUND(total_revenue / dau, 2) 注意除零防护和精度保留
子查询分层	基础指标在子查询先算好聚合结果，外层再做比率，逻辑清晰可复用

八、建表语句和数据插入

点击展开 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真题