留存率次日

例题1:计算11月每天新用户的次日留存率

思路：首先建立临时表计算每个用户的首次活跃日期

with t1 as(select uid,min(date(in_time)) as dt
from tb_user_log 
group by uid
),

建立临时表2将每个用户的进入时间和离开时间合并计算每个用户的全部活跃日期

t2 as (select uid,date(in_time) as dt
from tb_user_log
union
(select uid,date(out_time) as dt
from tb_user_log
order by uid))

建立表3，使用表1左连接表2，条件是用户名相同并且表1的日期等于表二的日期-1（次日留存） 

t3 as(select t1.uid as uid1,t1.dt as dt1, t2.uid as uid2,t2.dt as dt2
from t1 left join t2 
on t1.uid=t2.uid and t1.dt=date_sub(t2.dt,interval 1 day))

最终，计算次日留存

select dt1,round(count(uid2)/count(uid1),2) as uv_left_rate
from t3
where dt1 like'2021-11%'
group by dt1

例题2: