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

with t1 as (
    select distinct u.user_id,user_name,registration_date,plan_type,event_id,event_type,event_timestamp
    from users u join user_events ue on u.user_id = ue.user_id
    where registration_date between '2025-01-01 00:00:00' and '2025-06-30 23:59:59' and plan_type = 'Pro'
    and event_timestamp between '2025-01-01 00:00:00' and '2025-03-31 23:59:59' and event_type = 'login'
),
t2 as (
    select u.user_id,
    sum(if(event_type='create_task',5,if(event_type='export_report',10,if(event_type='invite_member',8,1)))) total_activity_score
    from users u left join user_events ue on u.user_id = ue.user_id
    group by u.user_id
),
t3 as (
    select u.user_id,round(count(event_type)/3,2) avg_monthly_events
    from users u left join user_events ue on u.user_id = ue.user_id
    where event_timestamp between '2025-01-01 00:00:00' and '2025-03-31 23:59:59'
    group by u.user_id 
)
select concat(user_name,'(',t1.user_id,')') user_profile,total_activity_score,avg_monthly_events
from t1 join t2 on t1.user_id = t2.user_id join t3 on t1.user_id = t3.user_id
order by total_activity_score desc,avg_monthly_events desc,t1.user_id