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

WITH t1 AS (SELECT user_id,min(l.date) earlist_date FROM login l GROUP BY 1),
t2 AS (SELECT t1.user_id,t1.earlist_date,CASE WHEN date(t1.earlist_date, '+1 day') IN 
       (SELECT date FROM login l WHERE t1.user_id=l.user_id) THEN 1 ELSE 0 END 
       AS liucun FROM t1),
t3 AS (SELECT t2.earlist_date,ROUND(SUM(t2.liucun)*1.0/COUNT(t2.liucun),3) p 
       FROM t2 GROUP BY 1)
SELECT t3.earlist_date date,t3.p 
FROM t3
UNION
SELECT date,0.000 AS p 
FROM login l 
WHERE date NOT IN (SELECT t3.earlist_date FROM t3)
ORDER BY 1;

比不上各位大佬，但是如果用各种子查询慢慢做，还是可以做出来的。 首先选出每个ID第一次登录的时间记为t1，其次，判断第一次登录后的日期加一天后是否还在原表中对应ID的日期里，是就记为1，否就是0. 然后，计算留存率。最后把剩余留存率为0的日期添加进去再排序就可以。