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

select dt,count(distinct uid) dau,
round(count(if(is_new=1,1,null))/count(distinct uid),2) uv_new_ratio
from
(select t1.uid uid,dt,first_date,
if(dt=first_date,1,0) is_new #判断当天是否为新用户
from
(select uid,date(in_time) dt
from tb_user_log
union
select uid,date(out_time) dt
from tb_user_log)t1
#用户活跃的所有日期，in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过
left join
(select uid,min(date(in_time)) first_date 
from tb_user_log
group by uid)t2#新用户表用户首次进入的时间
on t1.uid=t2.uid)t3#用户日志与新用户表左连接
group by dt
order by dt asc
;