请你写出一个sql语句查询每个日期新用户的次日留存率,结果保留小数点后面3位数(3位之后的四舍五入),并且查询结果按照日期升序排序
select date
,ifnull(
round(
(sum(case when (user_id,date_add(date, interval 1 day))in
(select user_id,date
from login)
then 1 else 0 end))
/
(sum(case when (user_id,date)in
(select user_id,min(date)from login group by user_id)
then 1 else 0 end)),
3),
0)as p
from login
group by date
order by date;
本题难度为困难,但是我愿称其为81道sql里面最难的一道,并且已知的题解里面非常混乱,我将代码分成了几个层次,便于理解。
题目要求查找某日期的新用户次日留存率,计算方法为:
当日后一天登录的记录数量/当日登录的新用户记录数量
如果当日没有新用户登录,则被除数为0,结果为null,故使用ifnull函数。
分子分母都通过group by date后sum和case when搭配计数,分母的计数原理易懂,通过group by和min(date)筛选当日新用户记录,
分子则是userid和date_add(date, interval 1 day)组合筛选,其中user_id在当前日期必有登录记录,date_add(date, interval 1 day)即为第二天的日期,两者组合即可筛选出第二日也登录的记录数量。
如果不懂可以评论,题目本身比较难,本题解代码量相对较小。