播面

面试题：2025年10月用户平均每周活跃天数统计

题目背景

在电商、社交或内容平台中，用户活跃度（度量用户粘性）是一个核心指标。本题要求根据 2025 年 10 月的用户日活明细表，统计在该月中活跃过的每个用户平均每周活跃多少天。

考察重点

1. 日期函数的使用：日期向周次（Week）的转换，特别是跨年、跨月的周处理。
2. 多层聚合（窗口/子查询）：先按“用户+周”聚合计算每周活跃天数，再按“用户”聚合计算平均值。
3. 边界条件处理：10 月的期初（10-01）和期末（10-31）可能处于非完整周内，如何正确归类周。

1. 示例数据

输入表：user_active_log（用户日活明细表）

注：表中仅包含 2025 年 10 月份及相邻的部分测试数据，实际计算时需过滤出 2025 年 10 月的数据。

2. 期望输出结果

3. SQL 代码实现 (MySQL 8.0 标答)

WITH user_weekly_stat AS (
    SELECT 
        user_id,
        -- 使用 YEARWEEK 将日期转换为“年-周”格式，模式 '1' 表示周一为一周的第一天
        YEARWEEK(active_date, 1) AS active_week,
        -- 统计该用户在这一周内活跃了多少天（去重防重入）
        COUNT(DISTINCT active_date) AS active_days_in_week
    FROM 
        user_active_log
    WHERE 
        -- 严格限制在 2025 年 10 月
        active_date >= '2025-10-01' AND active_date <= '2025-10-31'
    GROUP BY 
        user_id,
        YEARWEEK(active_date, 1)
)
SELECT 
    user_id,
    -- 计算该用户在10月活跃过的所有周的平均活跃天数，保留两位小数
    ROUND(AVG(active_days_in_week), 2) AS avg_weekly_active_days
FROM 
    user_weekly_stat
GROUP BY 
    user_id
ORDER BY 
    user_id;

4. 面试题深度分析与应对策略

在面试中遇到此题，候选人需要从以下几个维度展现自己的专业性：

核心考点一：如何定义“周”及处理跨月/跨年？

• 痛点：10月1日是周三，10月31日是周五。直接用 DAYOFMONTH 或简单的除以 7 会丢失真实的“周”物理边界。
• 解决方案：
  • 在 MySQL 中，推荐使用 YEARWEEK(date, 1)。其中参数 1 代表周一是一周的第一天（符合国内工作习惯），且它返回诸如 202540 的值，避免了跨年时首尾周重合的问题。
  • 如果是 Hive SQL，可以使用 weekofyear(active_date)。
  • 如果是 PostgreSQL，可以使用 TO_CHAR(active_date, 'IYYY-IW')（ISO标准周）。

核心考点二：业务指标口径的“分母定义”（关键加分项）

这是体现候选人业务sense的关键。平均每周活跃天数，分母（周数）怎么算？

• 口径 A (本代码采用)：仅除以该用户实际活跃过的周数。
  • 适用场景：分析用户在“生命周期内/有感知期间”的粘性。
  • 示例：用户 102 在10月只在第 42 周来了2天，其他周没来。平均每周活跃 2 天。
• 口径 B：除以 10 月份横跨的总物理周数（2025年10月横跨了5个自然周）。
  • 适用场景：宏观考量用户在整月的绝对活跃频次。
  • 示例：用户 102 平均每周活跃 2/5 = 0.4 天。
• 面试通关技巧：在写出代码前，主动向面试官确认口径：“请问这里的平均每周，分母是该用户在10月‘活跃过的周数’，还是10月份‘整体的物理周数（5周）’？” 这一问能立刻立住你的专业人设。

核心考点三：数据去重（Deduplication）

• 日活表在实际业务中可能存在重复数据（例如同一天用户多次登录产生的多条日志）。
• 在第一层聚合中，必须使用 COUNT(DISTINCT active_date)，而不是简单的 COUNT(1)，确保同一天多次活跃只算作 1 天。