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

with t as (
select user_id from users
join user_events using(user_id)
where (registration_date between '2025-01-01' and '2025-06-30') and plan_type = 'Pro' and (date_format(event_timestamp,'%Y-%m-%d') between '2025-01-01' and '2025-03-31')
group by user_id)

select concat(user_name,'(',user_id,')') as user_profile,
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,
round(sum(if(date_format(event_timestamp,'%Y-%m-%d') between '2025-01-01' and '2025-03-31',1,0)) / 3,2) as avg_monthly_events
from users
join user_events using(user_id)
join t using(user_id)
group by user_id,user_name
order by total_activity_score desc, avg_monthly_events desc,user_id