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

select date(a.in_time) as dt,
       count(distinct a.uid) as dau,
	   #如果b.uid有值，则说明当前日期是用户首次登陆日期
       round(count(case when b.uid is not null then 1 end)/count(distinct a.uid),2) as uv_new_ratio
from
# 将两个时间字段纵向合并
(select a1.uid,a1.in_time from tb_user_log a1 
union select a2.uid,a2.out_time as in_time from tb_user_log a2) a
left join
# 计算用户首次登陆日期
(select uid,min(in_time) as first_time from tb_user_log group by uid) b
on a.uid=b.uid and a.in_time=b.first_time
group by dt
order by dt asc