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

1. 求出用户活跃情况，用union去掉重复的记录 huoyue
2. 求用户第一次登陆的时期 new_user
3. 如果用户第一次登陆和活动时间一样就是新用户

with huoyue as (
    select 
        uid,
        date(in_time) as dt
    from tb_user_log
    union 
    select 
        uid,
        date(out_time) as dt
    from tb_user_log
    order by uid
),
new_user as (
    select 
        uid,
        min(dt) first_login
    from huoyue
    group by 1
)
select 
    dt,
    count(*) as dau,
    round(sum(if(first_login=dt,1,0))/count(*),2) as uv_new_ratio
from huoyue left join new_user using (uid)
group by dt
order by dt