题解 | #2021年11月每天新用户的次日留存率#

-- 使用left join和date_sub函数将每天对应的新用户和后一天仍来的用户关联在一起进行计算次日留存率
select a.dt ,round(count(b.uid)/count(a.uid),2) as uv_left_rate 
from (
-- 1、可以根据uid对应的最小进入日期来统计对应的新用户
select uid ,min(date(in_time)) as dt 
from tb_user_log 
group by uid ) a 
left join (
-- 2、由于可能存在用户进入时间和离开时间跨天的情况，所以需要uid对应的进入时间和离开时间合并在一起
select uid ,date(in_time) as dt 
from tb_user_log  
union 
select uid ,date(out_time) as dt 
from tb_user_log ) b 
on a.uid = b.uid 
and a.dt = date_sub(b.dt,interval 1 day) 
where left(a.dt,7) = "2021-11"
group by a.dt