SQL164 2021年11月每天新用户的次日留存率

SQL164 2021年11月每天新用户的次日留存率

思路

1. ​找出新用户​：确定每个用户首次活跃的日期（即新用户）

   • 例如101用户在11月1日首次出现

2. ​处理跨天活跃​：考虑用户可能跨天活跃的情况（in_time和out_time不在同一天）

   • 例如用户可能在11月1日23:50进入，11月2日00:10离开，则算作两天都活跃

3. ​计算次日留存​：

   • 对每个新用户，检查他们首次活跃后的第二天是否仍然活跃
   • 使用LEAD窗口函数高效获取用户下一次活跃日期

4. ​计算留存率​：

   • 每天的新用户数作为分母
   • 第二天仍然活跃的新用户数作为分子
   • 两者相除得到留存率，保留2位小数

最终输出2021年11月每天新用户的次日留存率，按日期排序。

代码

WITH 
-- 获取每个用户的最早活跃日期作为其首次出现日期
first_occurrence AS (
    SELECT 
        uid,
        DATE(MIN(in_time)) AS first_dt
    FROM tb_user_log
    GROUP BY uid
),

-- 获取每个用户每天活跃的记录（处理跨天情况）
user_active_dates AS (
    SELECT DISTINCT 
        uid,
        DATE(in_time) AS active_date
    FROM tb_user_log
    UNION
    SELECT DISTINCT 
        uid,
        DATE(out_time) AS active_date
    FROM tb_user_log
),

-- 为每个用户按日期排序，并使用LEAD获取下一天的活跃状态
user_activity_sequence AS (
    SELECT 
        uid,
        active_date,
        LEAD(active_date) OVER (PARTITION BY uid ORDER BY active_date) AS next_active_date
    FROM user_active_dates
),

-- 计算每天的新用户数及其次日留存情况
daily_stats AS (
    SELECT 
        fo.first_dt AS dt,
        COUNT(DISTINCT fo.uid) AS new_users,
        COUNT(DISTINCT CASE 
            WHEN DATEDIFF(uas.next_active_date, fo.first_dt) = 1 THEN fo.uid 
        END) AS retained_users
    FROM first_occurrence fo
    LEFT JOIN user_activity_sequence uas ON fo.uid = uas.uid AND fo.first_dt = uas.active_date
    WHERE fo.first_dt BETWEEN '2021-11-01' AND '2021-11-30'
    GROUP BY fo.first_dt
)

-- 计算并格式化留存率
SELECT 
    dt,
    ROUND(IF(new_users = 0, 0, retained_users / new_users), 2) AS uv_left_rate
FROM daily_stats
ORDER BY dt;

逐步展示如何计算2021年11月每天新用户的次日留存率

原始数据表 tb_user_log

步骤1：确定每个用户的首次活跃日期

SELECT 
    uid,
    DATE(MIN(in_time)) AS first_dt
FROM tb_user_log
GROUP BY uid;

结果：

步骤2：处理跨天情况，获取用户活跃日期

SELECT DISTINCT 
    uid,
    DATE(in_time) AS active_date
FROM tb_user_log
UNION
SELECT DISTINCT 
    uid,
    DATE(out_time) AS active_date
FROM tb_user_log;

结果：

步骤3：使用LEAD函数获取用户的下一次活跃日期

SELECT 
    uid,
    active_date,
    LEAD(active_date) OVER (PARTITION BY uid ORDER BY active_date) AS next_active_date
FROM user_active_dates;

结果：

步骤4：计算每天的新用户次日留存情况

SELECT 
    fo.first_dt AS dt,
    COUNT(DISTINCT fo.uid) AS new_users,
    COUNT(DISTINCT CASE 
        WHEN DATEDIFF(uas.next_active_date, fo.first_dt) = 1 THEN fo.uid 
    END) AS retained_users
FROM first_occurrence fo
LEFT JOIN user_activity_sequence uas ON fo.uid = uas.uid AND fo.first_dt = uas.active_date
WHERE fo.first_dt BETWEEN '2021-11-01' AND '2021-11-30'
GROUP BY fo.first_dt;

结果：

详细解释一下

这个CTE是计算每日新用户及其次日留存情况的核心部分，详细拆解逻辑：

1. ​数据来源​：

   • first_occurrence：包含每个用户的首次活跃日期
   • user_activity_sequence：包含用户每次活跃日期及下一次活跃日期(使用LEAD计算)

2. ​连接条件​：

   LEFT JOIN user_activity_sequence uas 
   ON fo.uid = uas.uid 
   AND fo.first_dt = uas.active_date

   • 按用户ID连接
   • 只连接用户首次活跃当天的记录（因为我们要计算的是新用户的次日留存）

3. ​计算字段​：

   • new_users：每天首次出现的用户数（COUNT DISTINCT）
   • retained_users：这些新用户中第二天仍然活跃的数量

4. ​留存判断逻辑​：

   CASE 
       WHEN DATEDIFF(uas.next_active_date, fo.first_dt) = 1 
       THEN fo.uid 
   END

   • 计算用户首次活跃日期与下一次活跃日期的差值
   • 如果差值为1天，则表示用户次日活跃

5. ​为什么用LEFT JOIN​：

   • 确保即使新用户第二天不活跃，也会被计入分母（新用户数）
   • 不活跃的用户在CASE WHEN中会返回NULL，不会被COUNT计算

示例数据推演

以2021-11-01为例：

• 新用户：101、102、103
• 检查他们的次日活跃情况：
  • 101：11-02活跃（符合）
  • 102：11-02不活跃
  • 103：11-02活跃（符合）
• 结果：3个新用户，2个次日活跃 → 留存率2/3=0.67

这种设计确保了：

1. 准确识别新用户
2. 正确处理跨天活跃情况
3. 精确计算次日留存率

最终结果：计算留存率

SELECT 
    dt,
    ROUND(IF(new_users = 0, 0, retained_users / new_users), 2) AS uv_left_rate
FROM daily_stats
ORDER BY dt;

最终输出：