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

select
    concat(a.user_name, '(', a.user_id, ')' ) as user_profile,
    b.total_activity_score,
    round(b.evt_times / 3, 2) as avg_monthly_events
from
    users a
    join (
        select
            user_id,
            count(case
                    when event_type = 'login' then 1
                end
            ) as login_times,
            count(event_type) as evt_times,
            sum(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 total_activity_score
        from user_events
        where date(event_timestamp) between '2025-01-01' and '2025-03-31'
        group by user_id
        having login_times >= 1
    ) b on a.user_id = b.user_id
where a.registration_date between '2025-01-01' and '2025-06-30' and a.plan_type = 'pro'
order by total_activity_score desc, avg_monthly_events desc, a.user_id