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

select 
    a.dt,
    ifnull(round(count(distinct b.uid)/count(a.uid),2),0) uv_left_rate
from 
    (
        select 
            uid,
            min(date(in_time)) dt
        from tb_user_log 
        group by uid
     ) a # 新用户首次登陆日期
left join 
    (
        select uid, date(in_time) dt from tb_user_log
        union          # 使用union去重合并登入登出表！！！！！！！！！！！ 而不是用union all不去重
        select uid, date(out_time) from tb_user_log
	) b
on a.uid = b.uid
   and a.dt = date_sub(b.dt, INTERVAL 1 day) # 即在b汇总表中，筛选新用户次日登录的日期数据
where date_format(a.dt,'%Y-%m') = '2021-11'
group by a.dt
order by a.dt