SQL_牛客网_SQL264_求每个登陆日期的次日留存率

牛客每个人最近的登录日期(五)

牛客每天有很多人登录，请你统计一下牛客每个日期新用户的次日留存率。
有一个登录(login)记录表，简况如下:
id user_id    client_id date
1    2    1    2020-10-12
2    3    2    2020-10-12
3    1    2    2020-10-12
4    2    2    2020-10-13
5    1    2    2020-10-13
6    3    1    2020-10-14
7    4    1    2020-10-14
8    4    1    2020-10-15
第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网，因为是第1次登录，所以是新用户
......
第4行表示user_id为2的用户在2020-10-13使用了客户端id为2的设备登录了牛客网，因为是第2次登录，所以是老用户
......
最后1行表示user_id为4的用户在2020-10-15使用了客户端id为1的设备登录了牛客网，因为是第2次登录，所以是老用户

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

查询结果表明:
2020-10-12登录了3个(user_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个(user_id为4)新用户，2020-10-15，user_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)
 

题目解法 

1.构建新用户临时表B

2.判断原表中是否存在与新用户临时表B,datediff()计算日期差值为1的记录，如果有则为新用户次日留存。

with t1 as (
    select user_id, min(date) min_date
    from login
    group by user_id
)
select 
    date,
    round(if(P,P,0),3) P 
from(
    select l1.date 'date', round(sum(if((l1.user_id,l1.date) in (select * from t1) and l2.user_id,1,0))/sum(if((l1.user_id,l1.date) in (select * from t1),1,0)),3) P
    from login l1 left join login l2
        on l1.user_id=l2.user_id and datediff(l2.date,l1.date)=1
    group by l1.date
    order by l1.date
)t2