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

-- 判断新老户
with is_new as(
    select
    uid
    ,date(min(in_time)) as dt
    from tb_user_log
    group by uid
)
, record as(
    select uid,date(in_time) as dt
    from tb_user_log

    UNION

    select uid,date(out_time) as dt
    from tb_user_log
)

select
a.dt 
,count(a.uid) as dau
,round(count(b.uid)/count(a.uid),2) as uv_new_ratio
from record a 
left join is_new b 
on a.uid=b.uid
and a.dt=b.dt
group by a.dt
order by a.dt