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

with t1 as
(select distinct ue.user_id
from user_events ue 
join users u using(user_id)
where (registration_date >= '2025-01-01' and registration_date<'2025-07-01') 
and plan_type ='Pro' 
and (event_timestamp >= '2025-01-01' and event_timestamp<'2025-04-01')
and event_type='login'),
t2 as
(select t1.user_id,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) total_activity_score
from t1
join user_events ue using(user_id)
group by 1),
t3 as
(select t1.user_id,round(count(*)/3,2) avg_monthly_events
from t1
join user_events ue using(user_id)
where event_timestamp >='2025-01-01' and event_timestamp<'2025-04-01'
group by 1)
select concat(user_name,'(',t2.user_id ,')') user_profile,total_activity_score,avg_monthly_events
from t2
join t3 using(user_id)
join users u using(user_id)
order by total_activity_score desc,avg_monthly_events desc,t2.user_id