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

继续笨办法，一步一步推
--------------------
with
t1 as(
    select dt
    from
    ((select date(in_time) as dt from tb_user_log group by date(in_time))
     union
     (select date(out_time) as dt from tb_user_log group by date(out_time))) a),
t2 as(
    select t1.dt,t0.uid
    from t1 left join tb_user_log t0 on (t1.dt=date(t0.in_time) or t1.dt=date(t0.out_time))
    where t0.id is not null group by t1.dt,t0.uid),
t3 as(select dt,count(distinct uid) as dau from t2 group by dt),
t4 as(
    select a.dt,a.uid
    from t2 a join t2 b on a.dt>b.dt and a.uid=b.uid group by a.dt,a.uid),
t5 as(
    select dt,count(uid) as uv_new from
    (select t2.dt,t2.uid
    from t2 where t2.uid not in(select t4.uid from t4 where t4.dt=t2.dt)) a group by dt)

select
    t3.dt,
    dau,
    coalesce(round(t5.uv_new/dau,2),0) as uv_new_ratio
from t3 left join t5 on t3.dt=t5.dt
order by t3.dt