题解 | #计算每天的新用户占比#

1. 关键知识点：min，avg，count，distinct，if，concat
2. 思路解析：先用min统计出每个用户最早登录日期，记为该用户作为新用户的日期；再用join将每次登录的记录中该用户作为新户日期关联起来，这里做了一个distinct操作是对一天登录多次的用户记录去重；最后按日期分组，计算每天登录日期为注册日期（即新用户）数量占该天总用户的占比，用round对结果后处理并concat一下百分号并排序。

select
  login_date as dt,
  count(1) as total_user_num,
  concat(
    round(100 * avg(IF(login_date = register_dt, 1, 0)), 1),
    "%"
  ) as new_user_rate
from
  (
    select
      distinct uid,
      login_date,
      register_dt
    from
      user_login_tb
      join (
        select
          uid,
          min(login_date) as register_dt
        from
          user_login_tb
        group by
          uid
      ) as t_reg_dt using(uid)
  ) as t_uid_dt
group by
  login_date
order by
  login_date;

遇到问题记得私聊哦