题解 | #各用户活跃分层人数统计#

1. 思路解析：
   ①统计每人最近一次和最早一次登录日期
   ②统计每人非当天的最近一次登录日期last_2_dt
   ③统计最大登录日期，记为当前日期
   ④统计总用户数
   ⑤计算每个用户最近登录日期、最早登录日期、非当天最近登录日期距今天数
   ⑥计算每人所属用户活跃层级
   ⑦统计每个层级的人数和占比
   ⑧按要求对结果后处理：排序并保留小数位数

SELECT
  user_grade,
  COUNT(uid) as num,
  ROUND(COUNT(uid) / MAX(user_cnt), 2) as ratio
FROM
  (
    SELECT
      uid,
      user_cnt,
      CASE
        WHEN last_dt_diff >= 30 THEN "流失用户"
        WHEN last_dt_diff >= 7 THEN "沉默用户"
        WHEN last_dt_diff = 0
          AND last2_dt_diff >= 31 THEN "回流用户"
        WHEN first_dt_diff < 7 THEN "新增用户"
        ELSE "忠实用户"
      END as user_grade
    FROM
      (
        SELECT
          uid,
          user_cnt,
          TIMESTAMPDIFF(DAY, last_2_dt, cur_dt) as last2_dt_diff,
          TIMESTAMPDIFF(DAY, last_dt, cur_dt) as last_dt_diff,
          TIMESTAMPDIFF(DAY, first_dt, cur_dt) as first_dt_diff
        FROM
          (
            -- 每个人最近一次活跃日期
            SELECT
              uid,
              MAX(login_date) as last_dt,
              MIN(login_date) as first_dt
            FROM
              user_login_tb
            GROUP BY
              uid
          ) as t_uid_first_last
          LEFT JOIN (
            -- 非当天的最近一次活跃日期
            SELECT
              uid,
              MAX(login_date) as last_2_dt
            FROM
              user_login_tb
            WHERE
              login_date != (
                select
                  max(login_date)
                from
                  user_login_tb
              )
            GROUP BY
              uid
          ) as t_uid_last_2_dt using(uid)
          LEFT JOIN (
            SELECT
              MAX(login_date) as cur_dt,
              COUNT(DISTINCT uid) as user_cnt
            FROM
              user_login_tb
          ) as t_overall_info ON 1
      ) as t_user_info
  ) as t_user_grade
GROUP BY
  user_grade
ORDER BY
  ratio DESC;