力扣1097(MySQL)-游戏玩法分析Ⅴ（困难）

题目：

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

我们还将某个日期 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

解题思路：

①先查询出每个用户的第一次登录时间install_dt;

SELECT 
    player_id, 
    event_date,
    MIN( event_date ) OVER ( PARTITION BY player_id ) AS install_dt 
FROM activity;

 ②在按install_dt分组，统计出 总人数与日存留人数

1）先判断datediff (event_date, install_dt ) = 1 就是1日留存率，有则为1个，否则为0，再sum求和，这样统计出1日存留率的个数；
2) 用count ( distinct play_id )求出分组的总人数，因为play_id有重复的，所以需要去重；
3）结果需要保留两位小数，故用round( ( 日存留率人数  /  总人数 ) , 2 )保留两位小数。

SELECT
    install_dt,
    count(DISTINCT player_id) as installs,
    round(sum(if(datediff(event_date,install_dt)=1,1,0))/count(DISTINCT player_id),2) as day1_retention
FROM
    ( SELECT 
        player_id, 
        event_date,
        MIN( event_date ) OVER ( PARTITION BY player_id ) AS install_dt 
     FROM activity) as a
GROUP BY install_dt;