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

# #统计新增用户
# select uid, min(date(in_time)) as min_dt from tb_user_log group by uid

# #统计用户活跃记录
# #如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过，结果按日期升序。
# #因为以上条件，我们需要把in和out拼接起来都算作该用户的活跃日期
# select uid, date(in_date) as dt from tb_user_log
# union 
# select uid, date(out_date) as dt from tb_user_log

#把新增用户和用户活跃记录关联，条件是 uid 和 min_dt+1
#得到表temp：用户, 最早登陆时间, 次日活跃时间
# with table temp as 
# (
# select a.uid, a.min_dt, b.dt 
# from 
# (select uid, min(date(in_time)) as min_dt 
# from tb_user_log 
# group by uid) a 
# left join 
# (select uid, date(in_date) as dt from tb_user_log
# union 
# select uid, date(out_date) as dt from tb_user_log) b
# on a.uid=b.uid and date_diff(b.dt, a.min_dt)=1
# )

# #最后求by天的留存率
# #留存率=count（新用户的次日活跃人数）/count（新用户人数）
# select min_dt as dt, round(count(dt)/count(min_date),2) as uv_left_rate
# from temp
# where min_dt like '2021-11%'
# group by min_date
# order by min_date

with temp as 
(
select a.uid, a.min_dt, b.dt 
from 
(select uid, min(date(in_time)) as min_dt 
from tb_user_log 
group by uid) a 
left join 
((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 b.dt=date_add(a.min_dt,interval 1 day)
)
select min_dt as dt, round(count(dt)/count(min_dt),2) as uv_left_rate
from temp
where min_dt like '2021-11%'
group by min_dt
order by min_dt