题解 | 每天的日活数及新用户占比
with t1 as(
select *,
min(date(in_time)) over(partition by uid) as first_in
from tb_user_log
# group by uid
),
t2 as(
select uid, date(in_time) as dt, first_in from t1
union
select uid, date(out_time) as dt, first_in from t1
),
t3 as(
select uid, dt,
if(datediff(dt,first_in)=0,1,0) as is_new
from t2
)
select dt,
count(uid) as dau,
round(sum(is_new)/count(uid),2) as uv_new_ratio
from t3
group by dt
order by dt asc
查看11道真题和解析
