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

WITH a AS(
    SELECT ue.user_id, user_name,
        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 score

    FROM users us
    JOIN user_events ue USING(user_id)
    WHERE registration_date BETWEEN '2025-01-01' AND '2025-06-30'
        AND plan_type = 'Pro' AND DATE(event_timestamp) BETWEEN '2025-01-01' AND '2025-03-31'
        AND ue.user_id IN(
            SELECT DISTINCT user_id
            FROM  user_events
            WHERE event_type = 'login'
        )
)

SELECT  CONCAT(user_name,'(', user_id,')') user_profile, SUM(score) total_activity_score, ROUND(COUNT(score)/3,2) avg_monthly_events
FROM a
GROUP BY 1,user_id
ORDER BY total_activity_score DESC, avg_monthly_events DESC,user_id