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

SELECT dt, num as dau, round(ifnull(new_uid,0) / num,2) as uv_new_ratio FROM ( SELECT dt, count(distinct uid) as num FROM ( SELECT uid, date(in_time) as dt from tb_user_log union all select uid, date(out_time) as dt from tb_user_log )a GROUP by dt)t1 left join (select new_date, count(distinct uid) as new_uid FROM ( select uid, min(date(in_time)) as new_date from tb_user_log group by uid )a group by new_date)t2 on t1.dt = t2.new_date