官网链接:
牛客每个人最近的登录日期(五)_牛客题霸_牛客网牛客每天有很多人登录,请你统计一下牛客每个日期新用户的次日留存率。 有一个登录(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日留存,也称为次日留存,代表的意思是:当日新增用户登录后,第二日继续登陆了。