以牛客面试题为例
SQL9 11月每天新用户的次日留存率
用户行为日志表tb_user_log 问题：统计11月每天新用户的次日留存率（保留2位小数）！！注：
次日留存率位当天新增的用户数中第二天又活跃了的用户数占比
如果in_time（进入时间）和out_time （离开时间） 跨天了，在两天里都记为该用户活跃过，结果按日期升序。
DROP TABLE IF EXISTS tb_user_log;CREATE TABLE tb_user_log (id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',uid INT NOT NULL COMMENT '用户ID',artical_id INT NOT NULL COMMENT '视频ID',in_time datetime COMMENT '进入时间',out_time datetime COMMENT '离开时间',sign_in TINYINT DEFAULT 0 COMMENT '是否签到') CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES(101, 0, '-11-01 10:00:00', '-11-01 10:00:42', 1),(102, 9001, '-11-01 10:00:00', '-11-01 10:00:09', 0),(103, 9001, '-11-01 10:00:01', '-11-01 10:01:50', 0),(101, 9002, '-11-02 10:00:09', '-11-02 10:00:28', 0),(103, 9002, '-11-02 10:00:51', '-11-02 10:00:59', 0),(104, 9001, '-11-02 10:00:28', '-11-02 10:00:50', 0),(101, 9003, '-11-03 11:00:55', '-11-03 11:01:24', 0),(104, 9003, '-11-03 11:00:45', '-11-03 11:00:55', 0),(105, 9003, '-11-03 11:00:53', '-11-03 11:00:59', 0),(101, 9002, '-11-04 11:00:55', '-11-04 11:00:59', 0);
分析
问题：统计11月每天新用户的次日留存率（问题扩大为计算每天的留存率，也可以是三日留存率或七日留存率问题
次日留存率=第二天继续活跃的人数/当天新增的用户总数
最终思想：得到每个用户的注册日期和对应的在线日期（考虑到在线日期可能跨天，所有要将登陆日期和退出日期量表进行纵向连接，即可说明这两天均为在线日期）
字段分解
每个用户对应的注册日期select uid,date(min(in_time)) 注册日期 from tb_user_log
得到每个用户对应的在线日期
…这里将可能会跨天的登录日期和退出日期都算进来了
select uid,date(in_time) 在线日期from tb_user_log) t1union select uid,date(out_time) 在线日期from tb_user_log) t2
以dt作为分组，以uid 相等，和第二天的用户等于第一天的用户 为条件连接两表
select * from (select uid,min(date(in_time)) dt from tb_user_log group by uid) t1left join select uid,date(in_time) dt from tb_user_log) t2on t1.uid=t2.uid ;
bcb80e71450.png)
-t1表为第一天出现，t2表中如果继续出现才会有值
再根据次日留存率=第二天继续出现的人数/当天的新用户。
select 注册日期,count(distinct t1.uid) 新增用户数,sum(datediff(在线日期,注册日期)=1) 次日留存用户数,--sum(datediff(t2.dt,t1.dt)<=3) 三日留存用户数,--sum(datediff(t2.dt,t2.dt)<=7) 七日留存用户数sum(datediff(在线日期,注册日期)=1)/count(distinct t1.uid) 次日留存率from(select uid,min(date(in_time)) 注册日期 #注册表from tb_user_log group by uid) t1left join #表2为，每个用户所有出现的日期(select uid,date(in_time) 在线日期from tb_user_log #登录表union select uid,date(out_time) 在线日期 from tb_user_log ) t2on t1.uid=t2.uid where date_format(注册日期,'%Y-%m')='-11'group by 注册日期order by 注册日期;
如果觉得《sql用户留存率细致讲解》对你有帮助，请点赞、收藏，并留下你的观点哦！
