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

select dt1,round( count(distinct if(datediff(dt2,dt1)=1 ,uid,null))/count(distinct if(uv=1,uid,null)),2)
from (
select  uid,dt as dt1,dense_rank() over(partition by uid order by dt) as uv,
lead(dt,1,dt) over(partition by uid order by dt) as dt2
from 
(
    select uid, date(in_time) as dt
    from tb_user_log
    union 
    select uid, date(out_time)
    from tb_user_log
)t2
#where substr(dt,1,7)='2021-11' 
#如果写在这里会排除第一天是上个月的情况，新用户就会定义错误 必须在排序后在写where
)t2
where uv=1 and substr(dt1,1,7)='2021-11'  
group by dt1