有一个登录(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 |
......
第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)