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

with a as (
    select t3.uid,t3.first_date,t3.login_date,datediff(t3.login_date,t3.first_date) as remain_date
    from (
            select distinct t2.uid,date(out_time) as login_date,t2.first_date from tb_user_log t1
            left join
            (select uid, min(date(in_time)) as first_date from tb_user_log group by uid) t2
            on t1.uid = t2.uid
            where substr(date(in_time),1,7) = '2021-11' and substr(first_date,1,7) = '2021-11'
        )t3
)select
    a.first_date dt, round(sum(if(remain_date = 1,1,0)) / count(distinct a.uid) ,2) as uv_left_rate
from a
group by dt
order by dt;