SQL编程：最近的登录日期

（1）题目描述

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

第1行表示id为2的用户在2020-10-12使用了客户端id为1的设备第一次新登录了牛客网
。。。

第4行表示id为3的用户在2020-10-12使用了客户端id为2的设备登录了牛客网

。。。

最后1行表示id为1的用户在2020-10-14使用了客户端id为2的设备登录了牛客网
请你写出一个sql语句查询新登录用户次日成功的留存率，即第1天登陆之后，第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入)，上面的例子查询结果如下:

查询结果表明:

id为1的用户在2020-10-12第一次新登录了，在2020-10-13又登录了，算是成功的留存

id为2的用户在2020-10-12第一次新登录了，在2020-10-13又登录了，算是成功的留存

id为3的用户在2020-10-12第一次新登录了，在2020-10-13没登录了，算是失败的留存

id为4的用户在2020-10-13第一次新登录了，在2020-10-14没登录了，算是失败的留存

固次日成功的留存率为 2/4=0.5

(sqlite里查找某一天的后一天的用法是:date(yyyy-mm-dd, '+1 day')，四舍五入的函数为round，sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5

mysql里查找某一天的后一天的用法是:DATE_ADD(yyyy-mm-dd,INTERVAL 1 DAY)，四舍五入的函数为round)

select 
round(count(distinct user_id)*1.0/(select count(distinct user_id) from login),3)
from login
where (user_id,date)
in (select user_id,DATE_ADD(min(date),INTERVAL 1 DAY) from login group by user_id);

(2)题目描述

牛客每天有很多人登录，请你统计一下牛客每个日期登录新用户个数，
有一个登录(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语句查询每个日期登录新用户个数，并且查询结果按照日期升序排序，上面的例子查询结果如下:


查询结果表明:
2020-10-12，有3个新用户(id为2，3，1)登录
2020-10-13，没有新用户登录
2020-10-14，有1个新用户(id为4)登录
2020-10-15，没有新用户登录

思路：利用MySQL中的窗口函数实现。

select 
a.date,
sum(case when ranks=1 then 1 else 0 end) new
from
(select date,row_number() over(partition by user_id order by date) ranks from login
) a
group by
date;

(3)题目描述

牛客每天有很多人登录，请你统计一下牛客每个日期新用户的次日留存率。
有一个登录(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 
a.date,
round(count(b.user_id)*1.0 / count(a.user_id),3) as p
from
(
    select user_id,
    min(date) as date
    from login
    group by
    user_id
) as a
left join
login b
on 
a.user_id=b.user_id
and
b.date=DATE_ADD(a.date,INTERVAL 1 DAY)
group by
a.date
union
select 
date,
0.000 as p
from
login
where
date 
not in
(
    select 
    min(date)
    from       
    login
    group by
    user_id
)
order by
date;