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

首先明确概念，当日留存率=明日老用户数/当日新用户数
即我们需要分别求老用户数和新用户数就可以。

【新用户数】

根据上一题求解：

select distinct date
        ,sum(case when (user_id,date) in 
    (select user_id,min(date)from login group by user_id)
    then 1 else 0 end)
from login
group by date

图片说明

【老用户数】

select date,count(user_id) 老用户数
from login
group by date

图片说明
看到这里已经完成80%，我将他们整理在一起，大家可能发现了以下规律：

新用户数为0，表示当日留存率肯定为0.
因此计算的时候，使用if语句，如果新用户数为0，则留存率直接写0.
新用户数不为0，我们需要用明日老用户数/当日新用户数
考虑到上下行的关系，即在不使用窗口函数的前提下（当然如果有小伙伴会用窗口函数，欢迎评论），只能想办法让老用户数往前提一天，便于计算。因此需要使用date_add函数
```
 date_add(date,interval -1 day)
```

如果往前提一天的话，有个问题，就没有2020-10-15的日期了。因此在进行两张表联合的时候，需要使用left关系。
全部代码如下：

select l.date,if(t2.新用户数 != 0,round(t1.老用户数/t2.新用户数,3),0)
from login l
left join (
  select count(user_id) 老用户数,date_add(date,interval -1 day) d1
  from login
  group by date
)t1 on t1.d1 = l.date
inner join(
  select date,sum(case when (user_id,date) in 
      (
          select user_id,min(date)
          from login 
          group by user_id) then 1 else 0 end
      ) 新用户数 
  from login
  group by date
)t2 on t2.date = l.date
group by date