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

select dt,dau,round(count(u_info.uid)/dau,2)
from
    (select dt,count(distinct uid) dau
    from
        (
        select date_format(in_time,"%Y-%m-%d")dt,uid
        from tb_user_log
        union all
        select date_format(out_time,"%Y-%m-%d")dt,uid
        from tb_user_log
        )t1
    group by dt
    ) dt_info 
left join
    (
    select uid,min(date_format(in_time,"%Y-%m-%d"))mindt
    from tb_user_log 
    group by uid
    ) u_info on dt_info.dt=u_info.mindt
group by dt,dau
order by dt