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

SELECT 
    CONCAT(user_name,'(',t1.user_id,')') user_profile,
    SUM(CASE event_type
            WHEN 'create_task' THEN 5
            WHEN 'export_report' THEN 10
            WHEN 'invite_member' THEN 8
            ELSE 1
        END) total_activity_score,
    ROUND(COUNT(IF(DATE(event_timestamp) BETWEEN '2025-01-01' AND '2025-03-31',event_type,NULL))/3,2) avg_monthly_events
FROM 
    users t1
    INNER JOIN user_events t2 ON t2.user_id=t1.user_id
WHERE 
    registration_date BETWEEN '2025-01-01' AND '2025-06-30'
GROUP BY 
    t1.user_id,
    user_name
HAVING 
    COUNT(IF(DATE(event_timestamp) BETWEEN '2025-01-01' AND '2025-03-31',event_type,NULL))>1
ORDER BY 
    total_activity_score DESC,
    avg_monthly_events DESC,
    t1.user_id