题解 | #2021年11月每天新用户的次日留存率#

SELECT
  dt,
  round(
    AVG(
      case
        when DATEDIFF(sed_day, dt) = 1 then 1
        else 0
      end
    ),
    2
  ) as uv_left_rate
FROM
(
    SELECT
      dt,
      uid,
      lead(dt, 1) over (
        PARTITION by uid
        ORDER BY
          dt
      ) sed_day,
      row_number() over(
        partition by uid
        ORDER BY
          dt
      ) nu
    FROM
(
        SELECT
          DATE_FORMAT(in_time, '%Y-%m-%d') dt,
          uid
        FROM
          tb_user_log
        UNION
        SELECT
          DATE_FORMAT(out_time, '%Y-%m-%d') dt,
          uid
        FROM
          tb_user_log
      ) t_1
  ) t_2
WHERE
  DATE_FORMAT(dt, '%Y-%m') = '2021-11'
  AND nu = 1
GROUP BY
  dt;

-- 解题思路

-- 1.合并时间序列，将进入时间和离开时间合并为一个时间序列。（对应问题：如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过）

-- 2.查找用户下一次出现的日期，因为下一天日期为次日，则为次日活跃。通过lead(dt,1) over (PARTITION by uid ORDER BY dt)实现。

-- 3.过滤获取用户第一次出现数据，因为用户第一次出现，才是次日活跃的计算范畴，因此，需要过滤取出第一次出现的数据。通过row_number() over(partition by uid ORDER BY dt)，nu =1。