题解 | #每天的日活数及新用户占比#
每天的日活数及新用户占比
https://www.nowcoder.com/practice/dbbc9b03794a48f6b34f1131b1a903eb
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 ;