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

select
utime dt, count(*) dau, round(sum(new_user)/count(*),2) uv_new_ratio
from(
    select
    b.uid, b.utime, if(a.firstday = b.utime, 1, 0) new_user
    from(
        select uid, date(min(in_time)) firstday
        from tb_user_log
        group by uid
    ) a left join(
        select uid, date(in_time) utime from tb_user_log
        union
        select uid, date(out_time) utime from tb_user_log
    ) b 
    on a.uid = b.uid 
) t
group by utime 
order by utime 

记录一下