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

首先建一个表temp以便之后使用。temp表记录的是用户id，用户最早登陆日期，以及最早登陆日期后一天有没有登陆。通过group by用户id搭配min(date)我们可以得到每个用户的最早登陆日期。然后用if来判断（user_id和最早登陆日期的后一天）这一对值是否在login表中，换句话说就是判断该用户在注册后第二天是否有登陆，登陆就标为1，没登录就标为0，并将这个boolean命名为next_day

with temp as
(select user_id, min(date) as min_date, 
 if((user_id,date_add(min(date), interval 1 day)) in 
    (select l.user_id, l.date from login as l), 1, 0) as next_day
 from login
 group by user_id)

然后将login表简写为l2并和temp通过left join连接，login表中全部信息得以保存并新增了次日留存的信息，这样方便处理无新增日期。用group by根据date分组汇总，然后对于有新增的日期，将next_day相加并除以当日用户总数量就能得到次日留存率，换句话说就是第二天还在的人数除以总人数。然后这里用if判断l2.date是否在temp表里，也就是判断login表里的某一天是否有新用户，若没有直接把当天留存率计为0.

select l2.date, 
if(l2.date in 
   (select t.min_date from temp as t), 
   round(sum(temp.next_day)/ count(temp.user_id),3), 0.000)
from login as l2 left join temp
on l2.date = temp.min_date and l2.user_id = temp.user_id
group by l2.date

以下是完整答案。

with temp as
(select user_id, min(date) as min_date, 
 if((user_id,date_add(min(date), interval 1 day)) in 
    (select l.user_id, l.date from login as l), 1, 0) as next_day
 from login
 group by user_id)
 
select l2.date, 
if(l2.date in 
   (select t.min_date from temp as t), 
   round(sum(temp.next_day)/ count(temp.user_id),3), 0.000)
from login as l2 left join temp
on l2.date = temp.min_date and l2.user_id = temp.user_id
group by l2.date