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

select in_time dt,count(is_new) dau,round(sum(is_new)/count(is_new),2) uv_new_ratio
from (select uid,in_time,
case
    when first_time = in_time then 1
    else 0
    end as is_new
from (select t1.uid,q1.first_time,t1.in_time
from (select uid,date(in_time) in_time
from tb_user_log
union select uid,date(out_time) in_time
from tb_user_log) t1
left join
(select uid,min(date(in_time)) first_time
from tb_user_log
group by uid) q1
on t1.uid = q1.uid) t2) t3
group by dt