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

法一：

SELECT ROUND(COUNT(t2.user_id)/ COUNT(t1.user_id),3) p FROM
(SELECT user_id, MIN(date) date FROM login GROUP BY user_id) t1
LEFT JOIN 
(SELECT DISTINCT user_id, DATE_SUB(date, INTERVAL 1 DAY) AS newdate 
 FROM login) t2
ON t1.user_id=t2.user_id AND t1.date=t2.newdate;

和这题很像https://blog.nowcoder.net/n/1eb2c32badec4f1cad27c7033aa2a742, 区别在于这里是求新用户的次日留存率，那里是求所有用户的次日留存率。

PS:就本题而言，登录表不会有user_id,date 重复的，去掉DISTINCT 也行。

法二，只要一个select然后加限定条件（从讨论区高赞处学习到）：

SELECT ROUND(COUNT(user_id)/(SELECT COUNT(DISTINCT user_id) FROM login),3)
FROM login
WHERE (user_id,date) IN (SELECT user_id,DATE_ADD(MIN(date),INTERVAL 1 DAY) 
                         FROM login GROUP BY user_id);