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

笨方法：
1、找出活跃用户表
2、找出新用户表
3、将以上两边左连接，然后计算最终结果，记得用ifnull
select dt,dau,round(ifnull(new_uv/dau,0),2) as uv_new_ratio
from
-- 3、活跃用户表
(select dt,count(1) 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) t3
group by dt) t4
left join

-- 2、计算每天新用户数
(select new_date,count(1) as new_uv
from -- 1、找到每个用户首日访问日期
    (select uid,min(date(in_time)) as new_date
    from tb_user_log
    group by uid) t1
group by new_date) t2
on t4.dt=t2.new_date
order by dt