【面试题】某游戏数据后台设有“登录日志”和“登出日志”两张表。
“登录日志”记录各玩家的登录时间和登录时的角色等级。
“登出日志”记录各玩家的登出时间和登出时的角色等级。
其中,“角色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”的数据:
至少登录过游戏一次表示登录过游戏即可,“登录日志”记录玩家的登录信息,玩家有登录过游戏就会有记录。
而在“登出日志”中玩家登出信息有可能缺失,因此,为了避免漏算玩家,我们从“登录日志”取数据进行查询:
如何计算玩家数呢?
计算玩家数即为计算玩家数量,而count()函数具有计数功能,因此我们使用count()函数来计算玩家数;玩家使用“角色id”唯一识别,一个“角色id”对应一位玩家,因此我们对“角色id”进行计数:
由于玩家在一天中可以多次登录游戏,登录日志中会存在重复的“角色id”,为了计算不重复玩家数,我们还需要使用distinct子句去重“角色id”,即计算不重复玩家数为:
将以上分析按SQL编写规范组合成完整的SQL语句即可计算出日活跃玩家数。
完整的SQL的书写方法:
查询结果如下:
2.次日留存率
因为次日留存率=次日仍登录的活跃玩家数/当日活跃玩家总数。
所以开服首日的次日留存率=开服次日(2022-08-14)仍登录的活跃玩家数/开服首日的活跃玩家总数。
开服首日的活跃玩家总数即开服首日游戏的DAU,在前面我们已经计算得出,现在我们来计算开服次日仍登录的活跃玩家数。
开服次日仍登录的活跃玩家数表示:开服首日登录过且在开服次日仍然登录的不重复玩家数。这里存在多个筛选条件:
1)从“登录日志”中筛选出开服次日(2022-08-14)登录的玩家:
2)并且玩家属于开服首日(2022-08-13)登录过的玩家:
2022-08-13登录过的玩家可以用以下SQL语句筛选出:
因此,从“登录日志”筛选开服次日仍登录的玩家的语句为:
筛选了玩家后就可以计算不重复玩家数了,计算不重复玩家数使用count(distinct 角色id)。
因此,计算开服次日仍登录的活跃玩家数的完整SQL语句的书写方法为:
查询结果如下:
现在,们在前面结果的基础上计算开服首日的次日留存率。
计算开服首日(2022-08-13)的活跃玩家总数的SQL的书写方法如下:
根据次日留存率的计算方法可知,计算次日仍登录的活跃玩家数的count(distinct 角色id)/计算首日登录的活跃用户数的count(distinct 角色id)即为次日留存率。
因此,可以将计算开服首日的活跃玩家总数的SQL语句带入计算开服次日仍登录的活跃玩家数的SQL语句中,进行除法运算。
完整SQL的书写方法:
查询结果如下:
即开服首日(2022-08-13)的次日留存率为75%。
案例数据下载途径:
本文来自邱碧兰投稿,不代表胡巴网立场,如若转载,请注明出处:https://www.hu85.com/363410.html
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 xxxxx@qq.com 举报,一经查实,本站将立刻删除。