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

select t2.dt,(allnum+if(num2 is not null,num2,0)) dau,round(if(xin is not null,xin,0)/(allnum+if(num2 is not null,num2,0)),2) uv_new_ratio from
(select date(in_time) dt,count(distinct uid) allnum
from tb_user_log
group by date(in_time)) as t2
left join 
(select date(time1) dt,count(1) xin
from(
select distinct uid,min(in_time) time1
from tb_user_log
group by uid) as t1
group by date(time1)) as t3
on t2.dt = t3.dt
left join
(select date(out_time) dt,count(distinct uid) num2
from tb_user_log
where timestampdiff(day,date(in_time),date(out_time)) = 1
group by date(out_time)) as t4
on t2.dt = t4.dt