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

SELECT
    CONCAT(u.user_name, '(', u.user_id, ')') AS user_profile,     -- 拼接字符
    SUM(
        CASE ue.event_type
            WHEN 'create_task' THEN 5              -- 变量赋值
            WHEN 'export_report' THEN 10
            WHEN 'invite_member' THEN 8
            ELSE 1
        END
    ) AS total_activity_score,
    ROUND(COUNT(ue.event_id) / 3, 2) AS avg_monthly_events   -- 记录事件id总数，记录事件平均数
FROM users u
INNER JOIN user_events ue            -- 内连接
    ON u.user_id = ue.user_id
WHERE 
    u.plan_type = 'Pro'
    AND u.registration_date BETWEEN '2025-01-01 00:00:00' AND '2025-06-30 23:59:59'
    AND ue.event_timestamp BETWEEN '2025-01-01 00:00:00' AND '2025-03-31 23:59:59'
GROUP BY u.user_id, u.user_name
HAVING SUM(CASE WHEN ue.event_type = 'login' THEN 1 ELSE 0 END) >= 1  
ORDER BY
    total_activity_score DESC,
    avg_monthly_events DESC,
    u.user_id ASC;