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

WITH user_score AS (
    SELECT 
        event_id,
        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,
        event_timestamp
    FROM user_events
    WHERE DATE(event_timestamp) BETWEEN '2025-01-01' AND '2025-06-30'
),
user_q1 AS (
    SELECT distinct user_id
    FROM user_events
    WHERE event_type='login' and DATE(event_timestamp) BETWEEN '2025-01-01' AND '2025-03-31'
)

SELECT 
    CONCAT(u.user_name, '(', u.user_id, ')') AS user_profile,
    SUM(activity_score) AS total_activity_score,
    ROUND(COUNT(case when DATE(event_timestamp) BETWEEN '2025-01-01' AND '2025-03-31' then us.event_id end)/3,2) AS avg_monthly_events
FROM user_score us
INNER JOIN users u ON us.user_id = u.user_id
INNER JOIN user_q1 u1 ON u1.user_id=u.user_id
WHERE 
    u.registration_date BETWEEN '2025-01-01' AND '2025-06-30' 
    AND u.plan_type = 'Pro'
GROUP BY user_profile,u.user_id
ORDER BY 
    total_activity_score DESC, 
    avg_monthly_events DESC,
    u.user_id ASC;