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

# 如果in_time-进入时间 和out_time-离开时间跨天了在两天里都记为该用户活跃过
# 使用 **union** 连接in_time 和 out_time找出in_time 和 out_time 不同的数据，
并把in_time 和 out_time相同的数据进行**去重**
with tmp as
(
    select
       uid,
       date_format(in_time, '%Y-%m-%d') cur_time
    from
        tb_user_log 
    union # 去重，并且保留in_time 和 out_time 不同的数据
    select
       uid,
       date_format(out_time, '%Y-%m-%d') cur_time
    from
        tb_user_log 

)

# 把登录的最小日期拼接到最后一列，用来判断是否为新增用户：当前日期 <= min_date 表示为新增用户
# select
#      tmp.uid,
#      cur_time,
#      min_date
#  from
#      tmp 
#  join
#      (
#          select
#              uid,
#              min(cur_time) min_date
#          from
#              tmp
#          group by
#              uid
#      )t
#  on 
#      tmp.uid = t.uid

select
    cur_time,
    dau,
    round(uv_new / dau, 2) uv_new_ratio
from
    (
        select    
            cur_time,
            count(*) dau,
            sum(if(cur_time <= min_date, 1, 0)) uv_new  # 当前日期 <= min_date 表示为新增用户 
        from
            (
              # 把登录的最小日期拼接到最后一列的子表
                select
                     tmp.uid,
                     cur_time,
                     min_date
                 from
                     tmp 
                 join
                     (
                         select
                             uid,
                             min(cur_time) min_date
                         from
                             tmp
                         group by
                             uid
                     )t
                 on 
                     tmp.uid = t.uid
            )tt
        group by 
             cur_time
    )t2