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

方法一：

WITH t1 AS(
SELECT date,COUNT(user_id) retention_num FROM login
WHERE (user_id,date) IN (SELECT user_id,DATE_ADD(MIN(date),INTERVAL 1 DAY) 
                         FROM login GROUP BY user_id)
GROUP BY date),//每个日期的新用户留存数
      t2 AS(
SELECT date, SUM(CASE WHEN rk=1 THEN 1 ELSE 0 END) new
FROM (SELECT date, rank() over (PARTITION BY user_id ORDER BY date) rk FROM login) tt2
GROUP BY date)//每个日期的新增用户数

SELECT t2.date, ROUND(IFNULL(retention_num/new,0),3) FROM t2 LEFT JOIN t1 
ON DATE_SUB(t1.date, INTERVAL 1 DAY)=t2.date
ORDER BY t2.date

（1）思路是先找每个日期的新用户留存数（分子，t1表），每个日期的新增用户数（分母，t2表），然后两个相除。（PS:不要纠结于找出每天具体是哪几个新用户，第二天哪几个用户留存，取交集此类的，只要分别知道总数就行了）

（2）要注意的是今天的留存率，是用第二天还登录的用户数除以今天的新用户数。举例，2020-10-12登录了3个新用户，2020-10-13还登录的有两个，故2020-10-12新用户次日留存率为2/3=0.667。

（3）结合“牛客每个人最近的登录日期(三)（四）”的做法。

（4）一些注解：第一，t1表的date被选择是因为它是新用户A的第二天，即A被留存了，所以COUNT(user_id)统计的是第二天还登录的用户数。第二，t2表的设置使得没有新增用户的日期也会记录下来（而t1表没有留存数的日期不会留下来），所以后面用t2表连接。第三，因为要日期错开除，所以要用DATE_SUB(t1.date, INTERVAL 1 DAY)=t2.date这样连接。

方法二（这个方法清楚些）：

WITH t1 AS(
SELECT date, COUNT(ttt2.user_id) retention_num FROM
(SELECT user_id, MIN(date) date FROM login GROUP BY user_id) ttt1
LEFT JOIN 
(SELECT DISTINCT user_id, DATE_SUB(date, INTERVAL 1 DAY) AS newdate 
 FROM login) ttt2
ON ttt1.user_id=ttt2.user_id AND ttt1.date=ttt2.newdate
GROUP BY date),//每个日期的新用户留存数
      t2 AS(
SELECT date, SUM(CASE WHEN rk=1 THEN 1 ELSE 0 END) new
FROM (SELECT date, rank() over (PARTITION BY user_id ORDER BY date) rk FROM login) tt2
GROUP BY date)//每个日期的新增用户数

SELECT t2.date, ROUND(IFNULL(retention_num/new,0),3) FROM t2 LEFT JOIN t1 
ON t1.date=t2.date
ORDER BY t2.date

（1）一些注解:这个方法是用t2左连接t1表。t1表的date被选择是因为它的第二天还有该用户，即用户被留存下来，而COUNT的是第二天还有的用户数（也就是今天的留存数，分子），所以后面t1和t2做连接的时候可以直接t1.date=t2.date这样连接，日期不用错开除。

t1表的做法：https://blog.nowcoder.net/n/e75ea677038c4df9828fc94fc02e0e50

t2表的做法：https://blog.nowcoder.net/n/478cb5399c364ee4b61b28987c9b49b6