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

## 统计每个用户的最小登入时间，记作该用户的新增用户时间，分别统计登入时间和登出时间；窗口函数统计 每个用户的
当前in_time和out_time是否和新增用户时间相等，若相等则记为1 代表当日的新增用户，否则记作0；在分别统计当天的新增用户个数，当天的活跃用户数，计算日活率并按照日期升序排列。

     select dt,
            count(uid) dau,
            round(sum(diff)/count(uid),2) uv_new_ratio
       from (
     select distinct uid,date_format(in_time,'%Y-%m-%d') dt,
            if(in_time=min(in_time)over(partition by uid order by in_time),1,0) diff
       from tb_user_log 
       union 
     select distinct uid,date_format(out_time,'%Y-%m-%d') dt,
            if(date_format(out_time,'%Y-%m-%d')=min(date_format(in_time,'%Y-%m-%d'))over(partition by uid order by in_time),1,0) diff
       from tb_user_log 
       ) A 
       group by dt 
       order by dt ;