题解 | 每天的日活数及新用户占比

#rihuo == mei tian , distinct, yonghuzongshu
#xinyonghu zhanbi=dangtian xinyonghu /rihuo
with tnew as (
    select date(tb.in_time) as date_in_time,count(distinct tb.uid) as newnb
    from tb_user_log tb
    where not exists(
        select 1
        from tb_user_log tbefore
        where tb.uid=tbefore.uid and date(tbefore.in_time)<date(tb.in_time)
    )
    group by date(tb.in_time)
), 
tdate as (
    select tb.uid as uid,date(tb.in_time) as in_time
    from tb_user_log tb
    union
    select tb.uid,date(tb.out_time)
    from tb_user_log tb   )



select date(tb.in_time) as dt, count(distinct tb.uid) as dau,coalesce(round(avg(t.newnb)/count(distinct uid),2),0) as uv_new_ratio
from tdate tb left join tnew t on date(tb.in_time) = t.date_in_time 
group by date(tb.in_time)
order by date(tb.in_time) asc