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

WITH sub AS (SELECT l.user_id,min(l.date) earlist_date
            FROM login l GROUP BY 1),
     sub2 AS (SELECT sub.user_id,date(sub.earlist_date,'+1 day') AS next_date
             FROM sub),
     sub3 AS (SELECT sub2.user_id,
            CASE WHEN sub2.next_date IN (SELECT date FROM login WHERE sub2.user_id=login.user_id) THEN 1 ELSE 0 END AS success
            FROM sub2)
SELECT ROUND(SUM(sub3.success)*1.0/COUNT(sub3.user_id),3) p 
FROM sub3

先选出每个用户ID登录最早的那个日期（也就是第一次登录的日期），然后给他们加一天，再去判断这个加一天以后的日期是否存在与原来的表中，存在就说明第二天也登录了，此时判断为1，不存在就说明留存失败判断为0. 最后用SUM（）/COUNT（），就可以知道留存率