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

分两步第一步求出每天的新用户，第二步求出每天的活跃用户，两个临时表合并操作即可

select NN.tm,NN.n,round(if(TT.mtm is null,0,TT.t / NN.n), 2) from
(select T.mtm,count(T.uid) t from
(select uid,min(date_format(in_time,"%Y-%m-%d")) mtm from tb_user_log 
group by uid) T group by T.mtm) TT
right join 
(select N.tm,sum(N.num) over(order by N.tm) n from
(select M.tm,sum(M.act) num from
(select date_format(in_time,"%Y-%m-%d") tm,uid, 1 act from tb_user_log tul union all
 select date_format(date_add(out_time, INTERVAL 1 DAY),"%Y-%m-%d") tm,uid,-1 act from tb_user_log tul)
 M group by M.tm) N ) NN
on TT.mtm = NN.tm
where NN.tm <> (select max(date_format(date_add(out_time, INTERVAL 1 DAY),"%Y-%m-%d")) from tb_user_log)