SQL突击战——游戏玩法分析 V

1097. 游戏玩法分析 V

#SQL架构
Create table If Not Exists Activity (player_id int, device_id int, event_date date, games_played int)
Truncate table Activity
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-01', '5')
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-02', '6')
insert into Activity (player_id, device_id, event_date, games_played) values ('2', '3', '2017-06-25', '1')
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '1', '2016-03-01', '0')
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '4', '2018-07-03', '5')

Activity 活动记录表

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+

（player_id，event_date）是此表的主键
这张表显示了某些游戏的玩家的活动情况
每一行表示一个玩家的记录，在某一天使用某个设备注销之前，登录并玩了很多游戏（可能是 0）

玩家的 安装日期 定义为该玩家的第一个登录日。

玩家的 第一天留存率 定义为：假定安装日期为 X 的玩家的数量为 N ，其中在 X 之后的一天重新登录的玩家数量为 M ，M/N 就是第一天留存率，四舍五入到小数点后两位。

编写一个 SQL 查询，报告所有安装日期、当天安装游戏的玩家数量和玩家的第一天留存率。

查询结果格式如下所示：

Activity 表：
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-03-02 | 6            |
| 2         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-01 | 0            |
| 3         | 4         | 2016-07-03 | 5            |
+-----------+-----------+------------+--------------+

Result 表：
+------------+----------+----------------+
| install_dt | installs | Day1_retention |
+------------+----------+----------------+
| 2016-03-01 | 2        | 0.50           |
| 2017-06-25 | 1        | 0.00           |
+------------+----------+----------------+
玩家 1 和 3 在 2016-03-01 安装了游戏，但只有玩家 1 在 2016-03-02 重新登录，所以 2016-03-01 的第一天留存率是 1/2=0.50
玩家 2 在 2017-06-25 安装了游戏，但在 2017-06-26 没有重新登录，因此 2017-06-25 的第一天留存率为 0/1=0.00

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/game-play-analysis-v
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

题解

该题目需要进一步的对第一天留存率进行解读：留存的玩家为第一天第一次登录，第二天第二次登录，流失玩家为第一天第一次登录，第二天没有登录，因此需要筛选出每个玩家前两天的登录状态。
首先用一个查询获取每个玩家每个日期是第几次登录，可以用partition by 和 order by配合row_number()实现。
以及今天登录了第二天是否也登录了，因此可以用开窗函数，对当前日期及后一天日期计数，如果第一天登录，第二天没登录，那么这个值为1；如果第二天也登录那么这个值为2。

在这个子表中，检索每个用户的第一次登录（log_num=1）的记录，并对日期进行分组，log_num=1表示用户在这一天进行了安装，save_flag=2表示留存，save_flag=1表示流失，因此对save_flag-1求和就可以得到留存的用户数。

select 
    tmp.event_date install_dt ,
    count(log_nums) installs , 
    round(sum(tmp.save_flag-1)/count(tmp.log_nums=1),2) Day1_retention 
 from
(
    select player_id,event_date,
    count(games_played) over (partition by player_id order by event_date range between current row and interval 1 day following) save_flag, # 是否存留
    row_number() over (partition by player_id order by event_date asc) log_nums # 登录次数
    from activity

) tmp  where log_nums=1 group by event_date order by event_date

语法笔记

interval 1 day

在开窗函数中，因为该列的数据类型为date_time所以在使用range between时需要用interval 1 day进行格式转换