题目描述
们将玩家的安装日期定义为该玩家的第一个登录日。
我们还将某个日期 X 的第 1 天保留时间定义为安装日期为 X 的玩家的数量,他们在 X 之后的一天重新登录,除以安装日期为 X 的玩家的数量,四舍五入到小数点后两位。
编写一个 SQL 查询,报告每个安装日期、当天安装游戏的玩家数量和第一天的保留时间。
查询结果格式如下所示:
玩家 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
正确答案
select a.install_dt,
count(a.player_id) as installs,
round(count(b.player_id)/ count(a.player_id),2) as Day1_retention
from
(
select player_id,
min(event_date) as install_dt
from Activity
group by player_id
) as a
left join Activity as b
on a.player_id=b.player_id and b.event_date-a.install_dt=1 group by a.install_dt
count(null)=0
思路
from子查询中,先找到所有玩家及其对应的登陆日期(a);
左连接原表,选出每个玩家event_date多于登陆日期1天的数据;