题解 | 每天的日活数及新用户占比
每天的日活数及新用户占比
https://www.nowcoder.com/practice/dbbc9b03794a48f6b34f1131b1a903eb
with t1 as(
select
distinct uid, date(in_time) dt, min(date(in_time))over(partition by uid) new_dt
from
tb_user_log
union
select
distinct uid, date(out_time) dt, min(date(in_time))over(partition by uid) new_dt
from
tb_user_log
)
select
dt, count(*) dau, round(sum(if(dt=new_dt,1,0))/ count(*), 2) uv_new_ratio
from
t1
group by
dt
order by
dt asc;