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

SELECT u_m_a.dt,
#     u_m_a.uid
    ROUND(COUNT(u_a.dt) / COUNT(u_m_a.dt), 2) AS uv_rate
FROM (
    -- 筛选每个用户的最早活跃日期
    SELECT uid,
        MIN(DATE(in_time)) AS dt
    FROM tb_user_log
    GROUP BY uid
)u_m_a
LEFT JOIN (
    -- 根据解释，当进入时间和离开时间跨天了，则记该用户次日留存
    -- 建立用户活跃表
    SELECT uid,
        DATE(in_time) AS dt
    FROM tb_user_log
    UNION
    SELECT uid,
    DATE(out_time) AS dt
    FROM tb_user_log
) u_a
ON u_m_a.uid = u_a.uid 
AND u_m_a.dt = DATE_SUB(u_a.dt, INTERVAL 1 DAY)
WHERE DATE_FORMAT(u_m_a.dt, '%Y-%m') = '2021-11'
GROUP BY u_m_a.dt
ORDER BY u_m_a.dt