题解 | #每天的日活数及新用户占比#
每天的日活数及新用户占比
https://www.nowcoder.com/practice/dbbc9b03794a48f6b34f1131b1a903eb
with t1 as (select uid,date(in_time)as dt,date(min(in_time)over(partition by uid)) as min_dt, case when date(in_time)=date(min(in_time)over(partition by uid)) then 1 else 0 end as new from tb_user_log UNION select uid,date(out_time)as dt,date(min(in_time)over(partition by uid)) as min_dt, case when date(out_time)=date(min(in_time)over(partition by uid)) then 1 else 0 end as new from tb_user_log order by uid, dt ) select dt,count(*) as dau,round(sum(new)/count(*),2) as uv_new_ratio from t1 group by dt