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

select
    t2.dt dt,
    count(t2.uid) as dau,
    round(count(t1.uid)/count(t2.uid), 2) uv_new_ratio
from
    (
        select
            uid,
            min(date (in_time)) dt
        from
            tb_user_log
        group by
            uid
    ) t1
    right join (
        select
            uid,
            date(in_time) dt
        from
            tb_user_log
        union 
        select
            uid,
            date(out_time)
        from
            tb_user_log
    ) t2
on t1.uid = t2.uid and t1.dt = t2.dt
group by
    t2.dt
order by
    t2.dt