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

with active as (
    select 
        uid,
        date(in_time) as dt,
        min(date(in_time))over(partition by uid) as new_d
    from 
        tb_user_log
    union
    select 
        uid,
        date(out_time) as dt,
        min(date(in_time))over(partition by uid) as new_d
    from 
        tb_user_log
)

select 
    dt,
    count(distinct uid) as dau,
    round(sum(case when dt = new_d then 1 else 0 end)/count(distinct uid),2) as uv_new_ratio
from active 
group by dt
order by dt