题解 | #每天的日活数及新用户占比#
每天的日活数及新用户占比
http://www.nowcoder.com/practice/dbbc9b03794a48f6b34f1131b1a903eb
分两步第一步求出每天的新用户，第二步求出每天的活跃用户，两个临时表合并操作即可
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)