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

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