播面 根据 2025 年 10 月的用户日活表，统计在该月活跃过的每个用户平均每周活跃多少天（需处理跨周和日期到周的转换）。
面试题：2025年10月用户平均每周活跃天数统计
题目背景
在电商、社交或内容平台中，用户活跃度（度量用户粘性）是一个核心指标。本题要求根据 2025 年 10 月的用户日活明细表，统计在该月中活跃过的每个用户平均每周活跃多少天。
考察重点
- 日期函数的使用：日期向周次（Week）的转换，特别是跨年、跨月的周处理。
- 多层聚合（窗口/子查询）：先按“用户+周”聚合计算每周活跃天数，再按“用户”聚合计算平均值。
- 边界条件处理：10 月的期初（10-01）和期末（10-31）可能处于非完整周内，如何正确归类周。
1. 示例数据
输入表：user_active_log（用户日活明细表）
注：表中仅包含 2025 年 10 月份及相邻的部分测试数据，实际计算时需过滤出 2025 年 10 月的数据。
| user_id | active_date (DATE) | 备注 (非表字段，辅助理解) |
|---|---|---|
| 101 | 2025-10-01 | 周三 (2025年第40周) |
| 101 | 2025-10-02 | 周四 (2025年第40周) |
| 101 | 2025-10-06 | 周一 (2025年第41周) |
| 101 | 2025-10-08 | 周三 (2025年第41周) |
| 101 | 2025-10-12 | 周日 (2025年第41周) |
| 101 | 2025-10-27 | 周一 (2025年第44周) |
| 102 | 2025-10-15 | 周三 (2025年第42周) |
| 102 | 2025-10-16 | 周四 (2025年第42周) |
| 103 | 2025-09-30 | 9月数据（不应纳入计算） |
| 103 | 2025-10-05 | 周日 (2025年第40周) |
| 103 | 2025-10-06 | 周一 (2025年第41周) |
| 103 | 2025-11-01 | 11月数据（不应纳入计算） |
2. 期望输出结果
| user_id | avg_weekly_active_days | 算法解析 |
|---|---|---|
| 101 | 2.00 | 第40周活2天，第41周活3天，第44周活1天。平均：(2+3+1)/3 = 2.00 天 |
| 102 | 2.00 | 第42周活2天。平均：2/1 = 2.00 天 |
| 103 | 1.00 | 10月内，第40周活1天(10-05)，第41周活1天(10-06)。平均：(1+1)/2 = 1.00 天 |
3. SQL 代码实现 (MySQL 8.0 标答)
sql
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标准周）。
- 在 MySQL 中，推荐使用
核心考点二：业务指标口径的“分母定义”（关键加分项）
这是体现候选人业务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 天。