牛客登录（5）

终于理解这题了，不容易。

 

牛客每天有很多人登录，请你统计一下牛客每个日期新用户的次日留存率。
有一个登录(login)记录表，简况如下:

第1行表示id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网，因为是第1次登录，所以是新用户
。。。
第4行表示id为2的用户在2020-10-13使用了客户端id为2的设备登录了牛客网，因为是第2次登录，所以是老用户
。。
最后1行表示id为4的用户在2020-10-15使用了客户端id为1的设备登录了牛客网，因为是第2次登录，所以是老用户



请你写出一个sql语句查询每个日期新用户的次日留存率，结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序，上面的例子查询结果如下:

查询结果表明:
2020-10-12登录了3个(id为2，3，1)新用户，2020-10-13，只有2个(id为2,1)登录，故2020-10-12新用户次日留存率为2/3=0.667;
2020-10-13没有新用户登录，输出0.000;
2020-10-14登录了1个(id为4)新用户，2020-10-15，id为4的用户登录，故2020-10-14新用户次日留存率为1/1=1.000;

2020-10-15没有新用户登录，输出0.000;

(注意:sqlite里查找某一天的后一天的用法是:date(yyyy-mm-dd, '+1 day')，sqlite里1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5)

 

 

 

 

 

select date
        ,ifnull(round((sum(case when (user_id,date)in
            (select user_id,date_add(date,interval -1 day) 
             from login)  and (user_id,date)in (select user_id,min(date)from login group by user_id)
            then 1 else 0 end))/
        (sum(case when (user_id,date)in
            (select user_id,min(date)from login group by user_id)
            then 1 else 0 end)),3),0)as p
from login
group by date
order by date;

select date
        ,ifnull(round((sum(case when (user_id,date)in
            (select user_id,date_add(date,interval -1 day)
             from login group by user_id)
            then 1 else 0 end))/
        (sum(case when (user_id,date)in
            (select user_id,min(date)from login group by user_id)
            then 1 else 0 end)),3),0)as p
from login
group by date
order by date;

两个代码均可过验证，但第一个更好，第二个代码只能保证这个人连续两天都登录了。需要注意的地方是

12号的留存率表示这个人，12号第一次登录，且13号也在。

之前犯的错是分子部分一直写的是：

case when (user_id,date)in
            (select user_id,date_add(min(date),interval 1 day)
             from login group by user_id

当时认为，id和日期需要在id+最小日期出现；且在id+(最小日期+1天)也出现。但是注意12号的留存率分子是12号这个人第一次登录，假如12号在date_add里，就说明这个id的最小日期是11号，因为11+1才等于12号，这就会计算错误