题解 | #牛客每个人最近的登录日期(五)#

一种超级笨超级麻烦的解法

首先因为题目是计算次日新用户的留存

思路是按照处理留存的方式对不同用户的连续日期进行排序,取出日期中的day减去对应的排名,得到一个结果,如果是同一个用户的连续登录天数的话,这个结果应该是相同的,因为是计算新用户的次日留存率,为避免统一用户的连续登录次数过多对处理造成影响,所以使用where将排名大于2的结果剔除

得到如下结果

然后因为是计算每日的次日留存,在这里我使用的是lead让后者减去前者,得到的结果有三种分别是0,null与其他, 当结果为0时,说明该用户为新用户且次日也进行了登录, 当结果为null时,说明该用户之后是没有进行登录的,且为老用户, 当结果为其他时,说明该用户是新用户,然后之后的登录天数并不是次日,(因为使用的是lead,是后者减去前者,又因为之前做了排名>3的筛选,所以有数值的地方应是新用户),处理完成后会得到如下结果

对应上表,拿到每日等于0的个数,该个数为每日新用户次日登录的人数,再拿到非空值(不为NULL)的个数,这个数则是每日新用户的总人数,相除即可

以下是代码:

SELECT date,
CASE
    WHEN `round` IS NULL THEN 0.000
    WHEN `round` IS NOT NULL THEN `round`
END
FROM
(SELECT date,ROUND(`sum`/`count`,3) AS `round`
FROM
(SELECT date,
SUM(CASE
    WHEN `num1`IS NOT NULL THEN 1
    WHEN `num1`IS NULL THEN 0
END) AS `count`,
SUM(CASE
    WHEN `num1`=0 THEN 1
    WHEN `num1`IS NULL THEN 0
END) AS `sum`
FROM
(SELECT date,user_id,
LEAD(`num`) OVER(PARTITION BY user_id)-`num` AS `num1`
FROM
(SELECT user_id,date,`day`,`rank`,
(`day`-`rank`) AS `num`
FROM
(SELECT *,DAY(date) AS `day`,
DENSE_RANK() OVER(PARTITION BY user_id
           ORDER BY date) AS `rank`
FROM login ) AS `A`
WHERE `rank`<3 ) AS `B`) AS `C`
GROUP BY date ) AS `D`) AS `E`