SQL<2021年11月每天新用户的次日留存率>的两种解法 作者: bluish 时间: 2025-03-04 分类: 笔记 #问题 题目：统计2021年11月每天新用户的次日留存率（保留2位小数） https://www.nowcoder.com/share/jump/7965046151741018052948 特殊限制： - 2021年11月 A - 每天新用户 B - 保留两位小数 C - 跨天标记活跃 D - 结果日期升序 E - 无新用户不输出 F #解法A 1. 每天新增的用户首次和下次的日期记录。输出首次、下次用户记录。**sub1** *A* 取最小范围数，可以用rank、group by[🔗链接](https://bluish.net/archives/2150/#menu_index_42) 通过排序窗口函数，筛选num<=2。 2. 判断当天新增用户的次日是否留存。输出首日、是否留存。**sub2** *B、D（在上个步骤也输出out_time，比较in_time和out_time判断跨日）* 通过date_add判断，标记if_stay 3. 计算每日新增用户的留存率。*C、E、F* 按date group by，计算留存率。 <pre> select date_in_time dt, round(sum(if_stay)/count(*),2) v_left_rate from( select uid, date_in_time, case when lead(date_in_time)over(partition by uid order by date_in_time)=date_add(date_in_time,interval 1 day) or date_out_time=date_add(date_in_time,interval 1 day) then 1 else 0 end if_stay, num from ( select distinct uid, date(in_time) date_in_time, date(out_time) date_out_time, dense_rank()over(partition by uid order by date(in_time)) num from tb_user_log )sub1 where year(date_in_time)=2021 and month(date_in_time)=11 and num<=2 order by date_in_time ) sub2 where num=1 group by date_in_time having count(*)<>0 order by date_in_time </pre> #解法B 1. UNION联合活跃日（包括in_time日与out_time跨日）*D* 2. 找出新用户首日。 3. Left Join连接表，and条件筛选留存。得到首日、活跃日的联合数据，将首日除以活跃日，得到次日活跃度。*A、B、C、E、F* <pre> select earliest_date, round(count(active_date)/count(earliest_date),2) uv_left_rate from ( select uid, min(date(in_time)) earliest_date from tb_user_log group by uid ) earliest left join( select uid, date(in_time) active_date from tb_user_log union select uid, date(out_time) from tb_user_log active_date ) active on earliest.uid=active.uid and active.active_date=date_add(earliest.earliest_date,interval 1 day) where month(earliest.earliest_date)=11 group by earliest_date </pre> #区别 解法A的视角是**「用户的下一次行为是否满足次日留存」**，属于「从用户行为序列中找规律」。 解法B的视角是**「用户的活跃日期是否包含次日的记录」**，通过 LEFT JOIN 将用户首日与次日活跃日期关联，属于「从日期维度反向匹配」。 SQL<2021年11月每天新用户的次日留存率>的两种解法 http://bluish.net/archives/2244/ 作者 bluish 发布时间 2025-03-04 许可协议 CC BY-SA 4.0 复制版权信息 标签: sql