题解 | #日活次日留存率和新户次日留存率#

1. 关键知识点：left join，date_sub，min/count，date_format，if，distinct，concat
2. 思路解析：
   ①用min取出每个用户作为新户的日期
   ②自关联上第二天也登录了的用户，生成一个新字段次日留存
   ③生成一个是否新户字段，逻辑是判断登录日期是否为新户日期
   ④用date_format对登录日期处理，筛选22年8月的记录
   ⑤计算次日留存率：次日又登录了的用户数除以当天登录了的用户数
   ⑥计算新户次日留存率：新户&次日又登录了的用户数除以新用户数
   ⑦对结果处理：按日期排序，并保留两位小数round(x, 2)

SELECT
  login_date,
  ROUND(COUNT(next_day_left) / COUNT(1), 2) as uv_left_rate,
  ROUND(
    COUNT(CONCAT(next_day_left, is_new_user)) / COUNT(is_new_user),
    2
  ) as new_uv_left_rate
FROM
  (
    SELECT
      distinct A.uid,
      login_date,
      delta_1_dt as next_day_left,
      IF(first_dt = login_date, 1, NULL) as is_new_user
    FROM
      user_login_tb as A
      LEFT JOIN (
        SELECT
          uid,
          DATE_SUB(login_date, INTERVAL 1 DAY) as delta_1_dt
        FROM
          user_login_tb
      ) as t_uid_dt_1 ON A.uid = t_uid_dt_1.uid
      AND A.login_date = t_uid_dt_1.delta_1_dt
      LEFT JOIN (
        -- 最早登录时间，即作为新户的日期
        SELECT
          uid,
          DATE(MIN(login_date)) as first_dt
        FROM
          user_login_tb
        GROUP BY
          uid
      ) as t_first_dt ON A.uid = t_first_dt.uid
      AND A.login_date = t_first_dt.first_dt
    WHERE
      DATE_FORMAT(login_date, "%Y%m") = "202208"
  ) as t_uv_new_info
GROUP BY
  login_date
ORDER BY
  login_date;