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

with idListUnion as (
    select * from (
(select uid,min(left(in_time,10)) as reg_time from tb_user_log group by uid)t1
left join (
select uid,left(in_time,10) as time from tb_user_log 
union 
select uid,left(out_time,10) as time from tb_user_log 
) t2
using(uid)
    )
)
select time,count(time),round(sum(isnewman)/count(time),2) from (
select uid,reg_time,time, if(reg_time = time,1,0) isnewman  from idListUnion
) as t1
group by time

本题难度是真的不大，主要难点在于要把每个人每天在线情况与注册时间进行一个对比，这样就知道哪些id在哪几天活跃，且注册的时间也知道，只要注册时间= 活跃时间那么就是当天新用户活跃，用1和0区分 即可，代码如上