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

with a as
#合并日期
(select
    uid,
    date(in_time) as dt
from    
    tb_user_log
union
select
    uid,
    date(out_time) as dt
from
    tb_user_log
),
#获取首次登录日期
b as (
select
    uid,
    min(dt) over(partition by uid) as first_date,
    dt
from
    a
)
#如果当前日期为首次登录日期则为新用户
select
    dt,
    count(distinct uid) as dau,
    round(count(distinct(if(first_date = dt, uid, null)))/count(distinct uid), 2) as uv_new_ratio 
from
    b
group by
    dt
order by
    dt