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

-- 第一步：建立临时表，利用窗口函数lead，取出每个日期对应的下一个日期数据(算次日留存)。因为可能会涉及跨天活跃问题，所以也需要out_time
with t as (
    select uid,date(in_time) as dint,date(out_time) as dout,
    lead(date(in_time))over(partition by uid order by in_time) as ldint
    from tb_user_log
)
-- 第二步：首先取出每个uid对应的首日(最小)登录日期，继而可以算留存率。注：要求的是2021年11月的留存率
select dint as dt,
round(if(sum(datediff(dout,dint)>=1 or datediff(ldint,dint)=1)/count(1) is null,0,sum(datediff(dout,dint)=1 or datediff(ldint,dint)=1)/count(1)),2) as uv_left_rate
from 
    (
    select uid,dint,dout,ldint
    from t
    where (uid,dint) in(select uid,min(dint) from t group by uid)
    ) t1
where date_format(dint,"%Y%m") = '202111'
group by dt
order by dt