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

with t1 as (
    select uid , date_format(in_time,'%Y-%m-%d') as time  from tb_user_log
    union -- 去重
    select uid , date_format(out_time,'%Y-%m-%d') as time  from tb_user_log
), -- 如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过
t2 as (
    select *, 
    row_number() over (partition by uid) as rn
    from t1
    ),
t3 as (
    select *,
    case when rn = 1 then time  end  as earliest
    from t2
    )
select time,
       count( distinct  uid) as dau, -- 去重count
       round(count(if(earliest is not null , 1 ,earliest)) / count(1),2 ) as uv_new_ratio
	   -- earliest 不为空 是新用户
from t3 group by time ;