题解 | #牛客每个人最近的登录日期(五)#

本题思路：

1. 找出所有用户首次登录的时间，并聚合出每天的新用户

		select
          a.date as date,
          count(*) as new_user_cnt
        from
          (
            select
              min(date) as date,
              user_id
            from
              login
            group by
              user_id
          ) a
        group by
          a.date

2. 判断每天新用户在第二天还剩多少用户，每个用户首次登录时间right join login表on次日登录的用户中还剩多少

		select
          a.date as date,
          count(*) as keep_user_cnt
        from
          (
            select
              min(date) as date,
              user_id
            from
              login
            group by
              user_id
          ) a
          right join login b on DATE_ADD(a.date, INTERVAL + 1 day) = b.date
          and a.user_id = b.user_id
        where
          a.user_id is not null
        group by
          a.date

3. 上述两张表聚合只能得到有留存数据的日期，还需要把其他没有新增用户的日期给join到表中，因此在将上述来两张表的聚合表与整个日期表聚合一下，得到完整的数据

# 当天新用户表
select
  a.date as date,
  IFNULL(b.p, 0.000) as p
from
  (
    select
      date
    from
      login
    group by
      date
    order by date
  ) a
  left join (
    select
      a.date as date,
      FORMAT(
        IFNULL(b.keep_user_cnt, 0.0) / IFNULL(a.new_user_cnt, 0.0),
        3
      ) as p
    from
      (
        select
          a.date as date,
          count(*) as new_user_cnt
        from
          (
            select
              min(date) as date,
              user_id
            from
              login
            group by
              user_id
          ) a
        group by
          a.date
      ) a
      inner join (
        select
          a.date as date,
          count(*) as keep_user_cnt
        from
          (
            select
              min(date) as date,
              user_id
            from
              login
            group by
              user_id
          ) a
          right join login b on DATE_ADD(a.date, INTERVAL + 1 day) = b.date
          and a.user_id = b.user_id
        where
          a.user_id is not null
        group by
          a.date
      ) b on a.date = b.date
  ) b on a.date = b.date;