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

with a as(
select date(in_time) as dt,uid,artical_id
from tb_user_log 
union 
select date(out_time) as dt,uid,artical_id
from tb_user_log ),

b as(
#新用户
select uid,min(dt) as first_date
 from a
 group by uid
)

select dt,count(distinct a.uid) as dau,
round(count(case when dt=first_date then 1 else null end)/count(distinct a.uid),2) as uv_new_ratio
from a
join b on a.uid=b.uid
group by dt

先写一个CTE a用来联立in_time和out_time

再写一个CTE b用来计算每一个用户的首次登陆日期

再联立俩表a和b得到 一个表（dt,uid,artical_id,first_date），从这个表里查询本日的新用户/日活用户数

（其实artical_id字段可以省略）