编写MySQL查询语句计算基于注册Cohort的用户周留存率

问题背景 #

需要编写MySQL查询语句，基于提供的events表和users表计算用户的周留存率，不确定是否需要同时使用这两张表。

数据表结构及数据 #

events表 #

users表 #

尝试过的错误查询 #

SELECT
  DATE(u.activated_at) AS cohort_start_date,
  WEEK(e.occurred_at) AS week_number,
  COUNT(DISTINCT e.user_id) AS active_users,
  COUNT(DISTINCT u.user_id) AS total_users,
  COUNT(DISTINCT e.user_id) / COUNT(DISTINCT u.user_id) * 100 AS retention_rate
FROM
  users u
JOIN events e ON u.user_id = e.user_id
WHERE
  e.event_type = 'engagement'
GROUP BY
  cohort_start_date, week_number
ORDER BY
  cohort_start_date, week_number;

问题分析与修正方案 #

原查询存在三个核心问题：

1. 关联逻辑错误：直接JOIN会过滤掉无engagement事件的用户，导致total_users统计的是有活动记录的用户数，而非对应cohort的总用户数。
2. 周数计算逻辑错误：仅取事件的周数，未与用户激活周做差值，无法体现“留存周数”（如激活后第0周、第1周）。
3. Cohort分组错误：按激活日期的天分组，而非用户激活所在的周，不符合周留存的统计逻辑。

正确的查询语句 #

周留存率定义为某周激活的用户中，在之后第N周仍有活跃行为的用户占比，需同时使用两张表：users划分用户cohort，events获取用户活跃记录。

-- 第一步：获取每个用户的激活周信息
WITH user_cohorts AS (
    SELECT
        u.user_id,
        -- 用年+周作为唯一cohort标识，避免跨年周重复
        DATE_FORMAT(u.activated_at, '%Y-%u') AS cohort_week,
        YEAR(u.activated_at) AS cohort_year,
        WEEK(u.activated_at) AS cohort_week_num
    FROM users u
),
-- 第二步：去重获取每个用户的活跃周
user_active_weeks AS (
    SELECT
        e.user_id,
        YEAR(e.occurred_at) AS active_year,
        WEEK(e.occurred_at) AS active_week_num,
        DATE_FORMAT(e.occurred_at, '%Y-%u') AS active_week
    FROM events e
    WHERE e.event_type = 'engagement'
    GROUP BY e.user_id, active_year, active_week_num, active_week
),
-- 第三步：计算每个cohort的用户留存情况
cohort_retention AS (
    SELECT
        uc.cohort_week,
        -- 计算活跃周与激活周的间隔，第0周为激活当周
        (ua.active_year - uc.cohort_year) * 52 + (ua.active_week_num - uc.cohort_week_num) AS week_interval,
        COUNT(DISTINCT uc.user_id) AS cohort_total_users,
        COUNT(DISTINCT ua.user_id) AS retained_users
    FROM user_cohorts uc
    LEFT JOIN user_active_weeks ua ON uc.user_id = ua.user_id
    GROUP BY uc.cohort_week, week_interval
)
-- 第四步：计算最终留存率
SELECT
    cohort_week,
    week_interval,
    cohort_total_users,
    retained_users,
    -- 处理除数为0的情况，保留两位小数
    ROUND(IF(cohort_total_users = 0, 0, retained_users / cohort_total_users * 100), 2) AS retention_rate
FROM cohort_retention
WHERE week_interval >= 0 -- 仅统计激活后的周数
ORDER BY cohort_week, week_interval;

关键说明 #

• Cohort划分：用%Y-%u格式的年周字符串作为cohort标识，解决跨年周数重复问题。
• 周间隔计算：通过年份差乘以52加周数差，确保跨年场景下的间隔计算准确。
• LEFT JOIN：保留所有激活用户，即使无后续活跃记录，保证cohort_total_users统计准确。
• 去重处理：对用户活跃周去重，避免同一用户一周内多次活跃被重复统计。

内容的提问来源于stack exchange，提问作者I Eat Nail Paint