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

WITH new_user AS (
  SELECT uid, DATE(in_time) AS daytime
  FROM (
    SELECT
      uid,
      in_time,
      ROW_NUMBER() OVER (PARTITION BY uid ORDER BY in_time) AS rn
    FROM tb_user_log
  ) t
  WHERE rn = 1
    AND DATE(in_time) BETWEEN '2021-11-01' AND '2021-11-30'
),
nu AS (
  SELECT daytime, COUNT(DISTINCT uid) AS new_user
  FROM new_user
  GROUP BY daytime
),
act AS (
  SELECT uid, DATE(in_time)  AS dt FROM tb_user_log
  UNION
  SELECT uid, DATE(out_time) AS dt FROM tb_user_log
),
d1 AS (
  SELECT
    n.daytime,
    COUNT(DISTINCT a.uid) AS act_user
  FROM new_user n
  JOIN act a
    ON a.uid = n.uid
   AND a.dt = DATE_ADD(n.daytime, INTERVAL 1 DAY)
  GROUP BY n.daytime
)
SELECT
  nu.daytime,
  ROUND(COALESCE(d1.act_user, 0) * 1.0 / NULLIF(nu.new_user, 0), 2) AS act_rt
FROM nu
LEFT JOIN d1
  ON nu.daytime = d1.daytime
ORDER BY nu.daytime;