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

select act_day as dt,
        count(*) as dau,
        round(sum(if_new)/count(*),2) as uv_new_ratio
from(
    select uid, date(in_time) as act_day,
            min(date(in_time)) over(partition by uid) as new_day,
            if(date(in_time)=min(date(in_time)) over(partition by uid) ,1,0) as if_new #当天新用户计为1，否则0
    from tb_user_log
    union 
    select uid, date(out_time) as out_day,
            min(date(in_time)) over(partition by uid),
            if(date(out_time)=min(date(in_time)) over(partition by uid) ,1,0) as if_new #当天新用户计为1，否则0
    from tb_user_log
) as t1
group by act_day
order by act_day