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

select
dt,
count(distinct uid) as dau,
round(sum(case when first_dt = dt then 1 else 0 end)/count(distinct uid), 2) as uv_new_ratio
from(
select
uid, date(in_time) as dt, min(date(in_time)) over(partition by uid) as first_dt
from tb_user_log
# group by 1,2
union
select
uid, date(out_time) as dt, min(date(in_time)) over(partition by uid) as first_dt
from tb_user_log
# group by 1,2
) tt
group by 1
order by 1
;