文章目录
前言1. 题目介绍2. 解题思路3. 解题过程3.1 计算次日留存数3.2 计算每个日期新用户个数3.3 两个结果表进行连接前言
本文主要记录牛客网真题-每个日期新用户的次日留存率计算的相关笔记，原题链接点击此处
1. 题目介绍
牛客每天有很多人登录，请你统计一下牛客每个日期新用户的次日留存率。
有一个登录(login)记录表，简况如下:
第1行表示user_id为2的用户在-10-12使用了客户端id为1的设备登录了牛客网，因为是第1次登录，所以是新用户
…
第4行表示user_id为2的用户在-10-13使用了客户端id为2的设备登录了牛客网，因为是第2次登录，所以是老用户
…
最后1行表示user_id为4的用户在-10-15使用了客户端id为1的设备登录了牛客网，因为是第2次登录，所以是老用户
请你写出一个sql语句查询每个日期新用户的次日留存率，结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序，上面的例子查询结果如下:
查询结果表明:
-10-12登录了3个(user_id为2，3，1)新用户，-10-13，只有2个(id为2,1)登录，故-10-12新用户次日留存率为2/3=0.667;
-10-13没有新用户登录，输出0.000;
-10-14登录了1个(user_id为4)新用户，-10-15，user_id为4的用户登录，故-10-14新用户次日留存率为1/1=1.000;
-10-15没有新用户登录，输出0.000;
(注意:sqlite里查找某一天的后一天的用法是:date(yyyy-mm-dd, ‘+1 day’)，sqlite里1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5)
2. 解题思路
1、首先应该计算出每个日期新用户的次日留存数
2、计算出每个日期登录新用户的个数
3、将两个表进行连接计算留存率
3. 解题过程
3.1 计算次日留存数
查找表中次日也有登录的当日记录，然后进行标记为（当日记录日期，记录个数），最后再次与原表连接，连接满足二者的日期相同，然后使用distinct关键字进行唯一值保留。代码如下：
select distinct l.date,IFNULL(w.u1,0)w_n from login l left join(select DATE_SUB(date,INTERVAL 1 DAY) date,COUNT(user_id) u1 from loginwhere (user_id,date)in(select user_id,DATE_ADD(date,INTERVAL 1 DAY) from logingroup by user_id)group by date)won l.date=w.date
3.2 计算每个日期新用户个数
对表的user_id进行group by，然后使用row_number()over()函数进行排序，之后添加一个新字段将每个user_id排序为1的那条记录命名为1，其他情况值为0，最后使用sum(case when)语句对每个日期下新用户个数继续累加。代码如下：
select t.date,sum(case when t.r1=1 then 1 else 0 end) t_n from (select user_id,date,row_number()over(partition by user_id order by date)r1 from login) t group by date
3.3 两个结果表进行连接
使用每个日期新用户的次日留存数除上每个日期的新用户个数即可。代码如下：
select w1.date,round(ifnull(w1.w_n/t1.t_n,0),3) from(select distinct l.date,IFNULL(w.u1,0)w_n from login l left join(select DATE_SUB(date,INTERVAL 1 DAY) date,COUNT(user_id) u1 from login where (user_id,date)in(select user_id,DATE_ADD(date,INTERVAL 1 DAY) from logingroup by user_id)group by date)won l.date=w.date)w1join(select t.date,sum(case when t.r1=1 then 1 else 0 end) t_n from (select user_id,date,row_number()over(partition by user_id order by date)r1 from login) t group by date)t1 on w1.date=t1.date
如果觉得《SQL-牛客网真题-统计一下每个日期新用户的次日留存率》对你有帮助，请点赞、收藏，并留下你的观点哦！
