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

select 
    t1.active   dt
    ,a         dau
    ,ifnull(round(b/a,2),0)  nv_new_ratio
from 

(
select 
    date(active) active 
    ,count(distinct uid) a
from 
    (select 
        uid
        ,in_time active
    from 
        tb_user_log
    union 
    select 
        uid
        ,out_time active
    from 
        tb_user_log) t
group by 
    date(active) 
) t1
left join 
(
select 
    min_date
    ,count(uid) b
from
(
select 
    uid 
    ,min(date(in_time)) min_date
from 
    tb_user_log 
group by 
    uid
) t0
group by 
    min_date
order by 
    min_date
) t2
on t1.active=t2.min_date