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

思路：

1. 分别创建用户新增表活和跃表，分别得到每天的新增用户数和活跃用户数
2. 通过日期联立两表，计算活跃率

with t1 as(
    select uid,date(in_time) dt
    from tb_user_log
    union
    select uid,date(out_time) dt
    from tb_user_log  
), -- 活跃表

t2 as(
    select
        uid,
        min(dt) as dt2
    from t1
    group by uid
) -- 新增表

select
    t1.dt,
    count(t1.uid) as dau,
    round(count(t2.uid)/count(t1.uid),2) as uv_new_ratio
from t1 left join t2 
on t1.uid=t2.uid and t1.dt=t2.dt2
group by dt
order by dt