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

SELECT
    t1.min_time AS dt,
    ROUND(COUNT(t2.uid) / COUNT(t1.uid), 2) AS sec_cnt
FROM(
    SELECT
        uid,
        MIN(DATE(in_time)) min_time
    FROM tb_user_log 
    GROUP BY uid
) t1
LEFT JOIN(
    SELECT uid, DATE(in_time) yestday FROM tb_user_log
    UNION
    SELECT uid, DATE(out_time) yestday FROM tb_user_log
) t2
ON t1.uid = t2.uid AND DATEDIFF(t2.yestday, DATE(t1.min_time)) = 1
WHERE month(min_time) = 11
GROUP BY dt

有两个要点

第一：通过UNION把in_time和out_time的纪录纵向拼在一起，并过滤相同的记录，如此一来就可以通过DATEDIFF函数筛选出与第一次登陆时间（使用MIN(DATE(in_time))查询得到）之间相差正好一天的记录。

第二：DATEDIFF(t2.yestday, DATE(t1.min_time)) = 1这一条件必须放在链接条件中，这样一来经过左连接，右表中不符合该条件的记录，t2.uid值将为NULL。在此基础上通过COUNT函数就能轻松得到第一天和第二天登录的人数（COUNT函数对NULL值自动跳过）。相反如果把这一条件放在WHERE中，则所有不符合该条件的记录将被直接过滤掉，就难以区分出第一、二天登录的人数。