牛客网SQL264：查询每个日期新用户的次日留存率
官网链接：
牛客每个人最近的登录日期(五)_牛客题霸_牛客网牛客每天有很多人登录，请你统计一下牛客每个日期新用户的次日留存率。 有一个登录(login。题目来自【牛客题霸】
https://www.nowcoder.com/practice/ea0c56cd700344b590182aad03cc61b8?tpId=82
0 问题描述
统计牛客每个日期新用户的次日留存率。有一个登录(login)记录表，简况如下:
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,1,2,'2020-10-13'),
(6,3,1,'2020-10-14'),
(7,4,1,'2020-10-14'),
(8,4,1,'2020-10-15');2 数据分析
方式一的完整代码如下：
select`date`,round(ifnull (count(next_user_id) / count(user_id), 0),3) as rate
from(selectt0.`date`,t1.user_id as user_id,t1.first_date,t2.user_id as next_user_id,t2.`date` as next_datefrom(select`date`fromlogingroup by`date`) as t0left join (selectuser_id,min(date) as first_datefromlogingroup byuser_id) t1 on t0.`date` = t1.first_dateleft join login as t2 on t1.user_id = t2.user_idand datediff (t2.`date`, t1.first_date) = 1order byt0.`date`) t3
group by`date`;上述代码分析
步骤一：使用左连接left join，得到所有第一次登陆的人及其第一次登录的日期
(selectuser_id,min(date) as first_datefromlogingroup byuser_id
) t1步驟二：使用datediff函数判断新增用户的次日登录情况
left join login as t2 on t1.user_id = t2.user_idand datediff (t2.`date`, t1.first_date) = 1步骤三：ifnull函数进行null值判断及补全
ifnull (count(next_user_id) / count(user_id), 0)因为不是每天都有新登录的用户，这些没有新登陆用户的日期，会导致分母为0，因此利用ifnul函数进行null值补全
3 小结
每日新增用户的1日留存，也称为次日留存，代表的意思是：当日新增用户登录后，第二日继续登陆了。