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

select
  NN.tm,
  NN.n,
  round(if(TT.mtm is null, 0, TT.t / NN.n), 2)
from
  (
    select
      T.mtm,
      count(T.uid) t
    from
      (
        select
          uid,
          min(date_format(in_time, "%Y-%m-%d")) mtm
        from
          tb_user_log
        group by
          uid
      ) T
    group by
      T.mtm
  ) TT
  right join (
    select
      N.tm,
      sum(N.num) over(
        order by
          N.tm
      ) n
    from
      (
        select
          M.tm,
          sum(M.act) num
        from
          (
            select
              date_format(in_time, "%Y-%m-%d") tm,
              uid,
              1 act
            from
              tb_user_log tul
            union all
            select
              date_format(date_add(out_time, INTERVAL 1 DAY), "%Y-%m-%d") tm,
              uid,
              -1 act
            from
              tb_user_log tul
          ) M
        group by
          M.tm
      ) N
  ) NN on TT.mtm = NN.tm
where
  NN.tm <> (
    select
      max(
        date_format(date_add(out_time, INTERVAL 1 DAY), "%Y-%m-%d")
      )
    from
      tb_user_log
  )