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

with user_table as(
    select 
        users.user_id,
        users.user_name
    from users join user_events using(user_id)
    where plan_type='Pro' 
        and date(registration_date) between '2025-01-01' and '2025-06-30 '
        and date(event_timestamp) between '2025-01-01' and '2025-03-31 '
        and event_type='login'
)
select 
    concat(user_name,'(',e.user_id,')') as user_profile,
    sum(case event_type when 'create_task' then 5
        when 'export_report' then 10
        when 'invite_member' then 8
        else 1 end ) as total_activity_score,
    round(cast(sum(case when date(event_timestamp) between '2025-01-01' and '2025-03-31 ' then 1 else 0 end) as DECIMAL(10, 2))/3 ,2)as avg_monthly_events
from user_events e join user_table t using(user_id)
group by user_name,e.user_id
order by total_activity_score desc,avg_monthly_events desc,user_id;