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

select
    concat(u.user_name, '(', u.user_id, ')') as user_profile,
    sum(
        case
            when ue.event_type = 'create_task' then 5
            when ue.event_type = 'export_report' then 10
            when ue.event_type = 'invite_member' then 8
            else 1
        end
    ) as total_activity_score,
    round(
        sum(
            case
                when ue.event_timestamp >= '2025-01-01'
                 and ue.event_timestamp < '2025-04-01'
                then 1
                else 0
            end
        ) / 3.0,
        2
    ) as avg_monthly_events
from
    users u
join
    user_events ue
    on u.user_id = ue.user_id
where
    u.plan_type = 'Pro'
    and u.registration_date >= '2025-01-01'
    and u.registration_date < '2025-07-01'
group by
    u.user_id,
    u.user_name
having
    sum(
        case
            when ue.event_type = 'login'
             and ue.event_timestamp >= '2025-01-01'
             and ue.event_timestamp < '2025-04-01'
            then 1
            else 0
        end
    ) >= 1
order by
    total_activity_score desc,
    avg_monthly_events desc,
    u.user_id;