题解 | #每天的日活数及新用户占比#
每天的日活数及新用户占比
http://www.nowcoder.com/practice/dbbc9b03794a48f6b34f1131b1a903eb
select dt,count(distinct t1.uid)as dau,
round(count(t2.uid)/count(distinct t1.uid),2) as uv_new_ratio
from
(select uid,date(in_time)as dt
from tb_user_log
union
select uid,date(out_time)as dt
from tb_user_log)t1
left join
(select uid,min(date(in_time))as f_d
from tb_user_log
group by uid)t2 on t1.uid=t2.uid and t1.dt=t2.f_d
group by dt
order by dt
round(count(t2.uid)/count(distinct t1.uid),2) as uv_new_ratio
from
(select uid,date(in_time)as dt
from tb_user_log
union
select uid,date(out_time)as dt
from tb_user_log)t1
left join
(select uid,min(date(in_time))as f_d
from tb_user_log
group by uid)t2 on t1.uid=t2.uid and t1.dt=t2.f_d
group by dt
order by dt
