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

with user_dau
as(
    select 
    uid,
    date(in_time) as dt
    from tb_user_log

    union 

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

user_sign 
as(
    select
    uid,
    date(min(in_time)) as first_time
    from tb_user_log
    group by 1
)

select
dt as dt,
count(distinct dau.uid) as dau,
round(sum(if(dt = first_time, 1, 0))/count(distinct dau.uid), 2)
from user_dau dau
left join user_sign sign 
on dau.uid = sign.uid
group by 1
order by 1