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

select
a.dt,
round(sum(case when datediff(b.dt,a.dt)=1 then 1 else 0 end)/count(distinct a.uid),2) 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
select uid,date(out_time) from tb_user_log) as b
on a.uid=b.uid and b.dt>a.dt
where month(a.dt)=11
group by a.dt

用sum（case when）和datediff可以算1，3，7日留存率