游戏行业实战案例1：日活跃率分析

【面试题】某游戏数据后台设有“登录日志”和“登出日志”两张表。

“登录日志”记录各玩家的登录时间和登录时的角色等级。

“登出日志”记录各玩家的登出时间和登出时的角色等级。

其中，“角色id”字段唯一识别玩家。

游戏开服前两天（2022-08-13至2022-08-14）的角色登录和登出日志如下

一天中，玩家可以多次登录登出游戏，请使用SQL分析出以下业务问题：

分析开服首日（2022-08-13），游戏的DAU（日活跃玩家数）和次日留存率（次日仍登录的活跃玩家数/当日活跃玩家总数）

【解题思路】

问题1：分析开服首日（2022-08-13），游戏的DAU（日活跃玩家数）和次日留存率（次日仍登录的活跃玩家数/当日活跃玩家总数）

1.计算开服首日游戏的DAU（日活跃玩家数）

游戏的DAU（日活跃玩家数），第一步就是要明确什么是日活跃玩家数，日活跃玩家数表示当日至少登录过游戏一次的不重复玩家数。

根据题意可知，当日即为开服首日（2022-08-13），因此，我们要用where子句筛选出日期为“2022-08-13”的数据：

where 日期 = '2022-08-13'

至少登录过游戏一次表示登录过游戏即可，“登录日志”记录玩家的登录信息，玩家有登录过游戏就会有记录。

而在“登出日志”中玩家登出信息有可能缺失，因此，为了避免漏算玩家，我们从“登录日志”取数据进行查询：

from 登录日志

如何计算玩家数呢？

计算玩家数即为计算玩家数量，而count()函数具有计数功能，因此我们使用count()函数来计算玩家数；玩家使用“角色id”唯一识别，一个“角色id”对应一位玩家，因此我们对“角色id”进行计数：

count(角色id)

由于玩家在一天中可以多次登录游戏，登录日志中会存在重复的“角色id”，为了计算不重复玩家数，我们还需要使用distinct子句去重“角色id”，即计算不重复玩家数为：

count(distinct 角色id) as 日活跃玩家数

将以上分析按SQL编写规范组合成完整的SQL语句即可计算出日活跃玩家数。

完整的SQL的书写方法：

select count(distinct 角色id) as 日活跃玩家数
from 登录日志
where 日期 = '2022-08-13';

查询结果如下：

2.次日留存率

因为次日留存率=次日仍登录的活跃玩家数/当日活跃玩家总数。

所以开服首日的次日留存率=开服次日（2022-08-14）仍登录的活跃玩家数/开服首日的活跃玩家总数。

开服首日的活跃玩家总数即开服首日游戏的DAU，在前面我们已经计算得出，现在我们来计算开服次日仍登录的活跃玩家数。

开服次日仍登录的活跃玩家数表示：开服首日登录过且在开服次日仍然登录的不重复玩家数。这里存在多个筛选条件：

1）从“登录日志”中筛选出开服次日（2022-08-14）登录的玩家：

select 角色id 
from 登录日志 
where 日期 = '2022-08-14';

2）并且玩家属于开服首日（2022-08-13）登录过的玩家：

and 角色id in (2022-08-13登录过的用户id)

2022-08-13登录过的玩家可以用以下SQL语句筛选出：

select 角色id 
from 登录日志 
where 日期 = '2022-08-13';

因此，从“登录日志”筛选开服次日仍登录的玩家的语句为：

select 角色id
from 登录日志
where 日期 = '2022-08-14' and 角色id in (
select 角色id 
from 登录日志 
where 日期 = '2022-08-13');

筛选了玩家后就可以计算不重复玩家数了，计算不重复玩家数使用count(distinct 角色id)。

因此，计算开服次日仍登录的活跃玩家数的完整SQL语句的书写方法为：

select count(distinct 角色id) as 次日仍登录的活跃玩家数
from 登录日志
where 日期 = '2022-08-14' and 角色id in (
select 角色id 
from 登录日志 
where 日期 = '2022-08-13');

查询结果如下：

现在，们在前面结果的基础上计算开服首日的次日留存率。

计算开服首日（2022-08-13）的活跃玩家总数的SQL的书写方法如下：

select count(distinct 角色id) 
from 登录日志 
where 日期 = '2022-08-13';

根据次日留存率的计算方法可知，计算次日仍登录的活跃玩家数的count(distinct 角色id)/计算首日登录的活跃用户数的count(distinct 角色id)即为次日留存率。

因此，可以将计算开服首日的活跃玩家总数的SQL语句带入计算开服次日仍登录的活跃玩家数的SQL语句中，进行除法运算。

完整SQL的书写方法:

select count(distinct 角色id)/(select count(distinct 角色id) from 登录日志 where 日期 = '2022-08-13') as 次日留存率
from 登录日志
where 日期 = '2022-08-14' and 角色id in (
select 角色id 
from 登录日志 
where 日期 = '2022-08-13');

查询结果如下：

即开服首日（2022-08-13）的次日留存率为75%。