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

select dt,count(uid) dau,
round(sum(if(dt=new_dt,1,0))/count(uid),2) uv_new_ratio
from
( select uid, min(date(in_time)) new_dt from tb_user_log
  group by uid 
) as tl right join 
( select uid,date(in_time) dt from tb_user_log
  union 
  select uid,date(out_time) dt from tb_user_log
) as t2 using(uid)
group by dt
order by dt;

虽然这回写的挺快，但看了第一个大佬的解题过程，感觉自己还是写复杂了。。。

再试试按大佬的思路再来一次

select dt,count(uid) dau,round(sum(if(dt=new_dt,1,0))/count(uid),2) uv_new_ratio from

(

select uid,date(in_time) dt,min(date(in_time))over(partition by uid) new_dt from tb_user_log

union

select uid,date(out_time) dt,min(date(in_time))over(partition by uid) new_dt from tb_user_log

) as t1

group by dt

order by dt;

合理使用窗口函数，少建很多表

;