新登录用户的次日成功的留存率

新登录用户的次日成功的留存率

描述

统计一下牛客新登录用户的次日成功的留存率。

有一个登录(login)记录表，简况如下:

请你写出一个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没登录了，算是失败的留存

示例1：

drop table if exists login;
CREATE TABLE `login` (
`id` int(4) NOT NULL,
`user_id` int(4) NOT NULL,
`client_id` int(4) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO login VALUES
(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,4,1,'2020-10-13'),
(6,1,2,'2020-10-13'),
(7,1,2,'2020-10-14');
0.500

解题

新登录用户的次日成功的留存率，首先把公式列出来:

（第一天登录的新用户并且第二天也登录的用户）/（总用户）即为新登录用户的次日成功的留存率

总用户其实挺好算，如下：

select count(distinct user_id) from login

找到每个用户第一天登陆的日子，其实挺好找，和前面找最近登录的日子差不多，一个是max，一个是min:

select user_id,min(date) from login group by user_id

比如上面查找语句是1，2020-10-12；那么如果找到一个结果为1，2020-10-13的那么是不是就符合结果了，于是可以如下写:

select user_id,date(min(date),'+1 day') from login group by user_id

这样就可以找到所有的在第一天登录的新用户并且第二天也登录的用户，以及第二天的日期。

所以从这个里面找到所有的count(distinct user_id)除以总用户就可以得到结果了，于是整个sql语句如下:

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(min(date),'+1 day') from login group by user_id);

mysql的解法为：

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);