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

with a1 as (
    select 
    user_id,
    user_name,
    sum(case when event_type='create_task' then 5
    when event_type='export_report' then 10
    when event_type='invite_member' then 8
    when event_type is null then 0
    else 1 end) as total_activity_score,
    round(sum(case when (date_format(event_timestamp,'%Y-%m') between '2025-01' and '2025-03') and event_type is not null then 1 else 0 end)/3 ,2) as avg_monthly_events
    from users
    join user_events
    using(user_id)
    where (date_format(registration_date,'%Y-%m') between '2025-01' and '2025-06')
    and plan_type='Pro'
    group by user_id,user_name
    having sum(case when (date_format(event_timestamp,'%Y-%m') between '2025-01' and '2025-03') and event_type='login' then 1 else 0 end)>0
)

select 
concat(user_name,'(',user_id,')') as user_profile,
total_activity_score,
avg_monthly_events
from a1
order by total_activity_score desc,avg_monthly_events desc,user_id