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

# 统计每天的日活数及新用户占比
# 新用户占比=当天的新用户数÷当天活跃用户数（日活数）。
# 如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过。
# 新用户占比保留2位小数，结果按日期升序排序。
with t1 as
(select 
    uid,
    date(in_time) as dt
from tb_user_log
union
select 
    uid,
    date(out_time) as dt
from tb_user_log),
t2 as
(select 
    uid,
    dt,
    min(dt) over(partition by uid) as mindt
from t1),
t3 as
(select
    uid,
    dt,
    if(dt=mindt,1,0) as newu
from t2)
select
    dt,
    count(distinct uid) as dau,
    round(sum(newu)/count(distinct uid), 2) as uv_new_ratio
from t3
group by dt
order by dt