牛客每天有很多人登录,请你统计一下牛客新登录用户的次日成功的留存率,
有一个登录(login)记录表,简况如下:
第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备第一次新登录了牛客网
。。。
请你写出一个sql语句查询新登录用户次日成功的留存率,即第1天登陆之后,第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入),上面的例子查询结果如下:
有一个登录(login)记录表,简况如下:
第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备第一次新登录了牛客网
。。。
第4行表示user_id为2的用户在2020-10-12使用了客户端id为2的设备登录了牛客网
。。。
最后1行表示user_id为1的用户在2020-10-14使用了客户端id为2的设备登录了牛客网
请你写出一个sql语句查询新登录用户次日成功的留存率,即第1天登陆之后,第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入),上面的例子查询结果如下:
查询结果表明:
user_id为1的用户在2020-10-12第一次新登录了,在2020-10-13又登录了,算是成功的留存
user_id为2的用户在2020-10-12第一次新登录了,在2020-10-13又登录了,算是成功的留存
user_id为3的用户在2020-10-12第一次新登录了,在2020-10-13没登录了,算是失败的留存
user_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)
解题:
1. 求开始第一天和第二天都登录的
2. 总登录数
3. 把 结果 1 除以 结果 2
方法一:
SELECT ROUND(COUNT(DISTINCT user_id) * 1.0 / (SELECT COUNT(DISTINCT user_id) FROM login), 3) AS p --当初也没想到可以这么用子查询;* 1.0 作用是将数转为小数 FROM login WHERE (user_id, date) IN (SELECT user_id, DATE_ADD(MIN(date), INTERVAL 1 DAY) --登录2次,选最小日期为首次登陆,加一日就是第二天也登录(有第二天的数据,表示第一天是必须有登录的) FROM login GROUP BY user_id );
方法二:
SELECT ROUND(COUNT(nextdate)/COUNT(firstdate),3) --步骤2,俩个数相除 FROM (SELECT l1.user_id, MIN(l1.date)as firstdate, l2.date as nextdate FROM login l1 LEFT JOIN login l2 ON l1.user_id = l2.user_id AND l2.date = l1.date + 1 --步骤1,求出首次登陆和次日登录的数据 GROUP BY l1.user_Id) as T
步骤1,求出首次登陆和次日登录的数据
步骤2,俩个数相除
u_id | first_date |
next_date |
2 | 2020-10-12 |
2020-10-13 |
1 |
2020-10-12 |
2020-10-13 |
3 |
2020-10-12 |
Null |
4 |
2020-10-13 |
Null
|