【SQL34】计算至少连续两天登录的玩家数，把这个数字除以玩家总数

编写一个SQL查询，报告在首次登录后第二天再次登录的玩家的分数，四舍五入到小数点后两位。换句话说，你需要从首次登录日期开始计算至少连续两天登录的玩家数，把这个数字除以玩家总数。

查询结果格式如下所示：
Activity 表:

Result 表:

Result 表:

解释：只有ID为1的玩家在第一天登录后才重新登录，所以答案是1/3=0.33

 

解决：

SELECT round((SELECT COUNT(DISTINCT b.player_id)
                FROM activity AS b
                    ,activity AS c
               WHERE b.event_date =
                     (SELECT MIN(temp.event_date) FROM activity AS temp WHERE temp.player_id = b.player_id)
                 AND b.player_id = c.player_id
                 AND datediff(b.event_date, c.event_date) = -1) / COUNT(DISTINCT a.player_id),
             2) AS 'fraction'
  FROM activity AS a;