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

# 先找到新用户
# 在找次日登录用户（用户登录表，涵盖进入时间和离开时间跨天情况）
# 最后计算次日留存 COUNT(tb1.uid) / COUNT(tb2.uid)
SELECT
    reg_dt AS dt,
    ROUND(COUNT(tb2.uid) / COUNT(tb1.uid), 2) AS uv_left_rate
FROM(
SELECT
    uid,
    MIN(DATE(in_time)) AS reg_dt
FROM tb_user_log
GROUP BY uid
) AS tb1 # 找到新用户表
LEFT JOIN (
    SELECT 
        uid,
        DATE(in_time) AS log_dt
    FROM tb_user_log
    UNION
    SELECT
        uid,
        DATE(out_time) AS log_dt
    FROM tb_user_log
) AS tb2 # 用户登录表，涵盖进入时间和离开时间跨天情况
ON tb1.uid = tb2.uid AND tb2.log_dt = DATE_ADD(tb1.reg_dt, INTERVAL 1 DAY) # 条件3：次日留存，登录时间=注册时间+1天
WHERE DATE_FORMAT(reg_dt, '%Y-%m') = '2021-11' # 条件1
GROUP BY reg_dt
ORDER BY dt ASC # 条件2