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

#需求：统计2021年11月每天新用户的次日留存率
#输出：日期、次日留存率
#要求：时间范围date_format(out_time,'%Y-%m')=2021-11；uv_left_rate保留两位小数，dt升序输出
#特殊点：次日留存率=新增用户x+1日活跃数/x日新增用户数；in_time与out_time如果跨天，两天都计为活跃
#拆分问题：新增用户：查找用户的第一次登录时间，在那天他即为新增用户
#进入时间与离开时间跨天则两天都计为活跃：union，A为in_time，B为out_time
#次日留存：date_sub减去一天
select t1.dt,round(count(t2.dt)/count(t1.dt),2) uv_left_rate
from(
  select uid,min(date(in_time)) dt
  from tb_user_log
  group by uid
) t1
#得到每个用户的首次登录日期
left join(
  select uid,date(in_time) dt from tb_user_log
  union
  select uid,date(out_time) dt from tb_user_log
) t2 on t1.uid=t2.uid
#得到所有用户的活跃日期
and t1.dt=date_sub(t2.dt,interval 1 day)
where date_format(t1.dt,'%Y-%m')='2021-11'
#这里使用左链接并附带筛选条件date_sub，只有该用户第二天依然有记录的才会保留t2表的数据
#表链接后，得到的数据是用户ID、首次登录日期、留存次日日期，如果次日未留存则次日日期为null
#依据得到的数据，可以直接count计数相除，count会剔除null值，只计算有次日日期的，得到的就是次日留存率
group by 1
order by 1