题解 | SaaS产品高价值用户活跃度分析

WITH tb1 AS (
    SELECT user_id
    FROM user_events
    WHERE user_id IN (
        SELECT user_id
        FROM users
        WHERE plan_type = 'Pro'
        AND registration_date BETWEEN '2025-01-01' AND '2025-06-30'
    ) 
    AND event_type = 'login'
    AND event_timestamp >= '2025-01-01' AND event_timestamp < '2025-04-01'
),
tb2 AS (
    SELECT user_id, 
    CASE WHEN event_type = 'create_task' THEN 5
         WHEN event_type = 'export_report' THEN 10
         WHEN event_type = 'invite_member' THEN 8
         ELSE 1 END AS activity_score,
    IF(event_timestamp >= '2025-01-01' AND event_timestamp < '2025-04-01', 1, 0) AS is_q1_event
    FROM user_events 
    WHERE user_id IN (SELECT user_id FROM tb1)
)
SELECT  CONCAT(users.user_name, '(', tb2.user_id, ')' )  AS user_profile, 
        SUM(activity_score) AS total_activity_score,
        ROUND(SUM(tb2.is_q1_event) / 3, 2) AS avg_monthly_events
FROM tb2
LEFT JOIN users USING (user_id)
GROUP BY user_name, user_id
ORDER BY total_activity_score DESC, avg_monthly_events DESC, user_id ASC

第一步：精准画像筛选（准入逻辑 tb1）

这一步是为了找出“谁才是我们要统计的人”。

• 注册维度：必须是 2025 年上半年（1月-6月）注册的 'Pro' 计划用户。
• 行为维度：必须在 2025 年第一季度（Q1）内至少有过一次 'login' 行为。
• 关键点：你使用了 event_timestamp < '2025-04-01'，确保了 3 月 31 日晚上的登录行为不会被漏掉，解决了之前 Grace(202) “失踪”的问题。

第二步：多维度指标预处理（打分逻辑 tb2）

由于积分和月均事件数的统计时间范围不同，你在这里做了一个非常巧妙的宽表预处理：

• 全量积分：不对 event_timestamp 做总过滤，确保 Alice 四月份的 invite_member（8分）能够计入 total_activity_score。
• 标签化处理：通过 IF 函数创建了 is_q1_event 辅助列。如果是 Q1 的事件记为 1，否则为 0。这为你后续在同一行 SQL 里计算不同时间跨度的指标打下了基础。

第三步：结果聚合与格式化（呈现逻辑）

最后通过 LEFT JOIN 回 users 表获取用户名，并进行最终计算：

• 指标 1：user_profile：使用 CONCAT 函数将用户名和 ID 拼接，满足了特定的输出格式。
• 指标 2：total_activity_score：对 tb2 全量数据求和。
• 指标 3：avg_monthly_events：仅对 is_q1_event 标记为 1 的记录求和（即 Q1 总次数）再除以 3。
• 多级排序：严格执行了“积分降序 > 月均降序 > ID 升序”的三重规则。