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

with pro_user_id as (
    select distinct
    user_id,
    user_name
    from
    users
    where registration_date between '2025-01-01' and '2025-06-30'
    and plan_type='Pro'
),
login_user_id as (
    select distinct
    user_id
    from
    user_events
    where date(event_timestamp) between '2025-01-01' and '2025-03-31'
    and event_type='login'
),
true_user_id as (
    select
    p.user_id,
    user_name
    from
    pro_user_id p
    inner join
    login_user_id l on p.user_id=l.user_id
),
tb_activity_score as(
select
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) as total_activity_score
from
user_events
group by user_id
),
average_event_cnt as (
    select
    user_id,
    count(*)/3 as avg_monthly_events
    from
    user_events
    where date(event_timestamp) between '2025-01-01' and '2025-03-31'
    group by user_id
)

select
concat(u.user_name,'(',u.user_id,')') as user_profile,
total_activity_score,
round(avg_monthly_events,2) as avg_monthly_events
from
true_user_id u
inner join
tb_activity_score t on u.user_id=t.user_id
inner join
average_event_cnt a on u.user_id=a.user_id
order by total_activity_score desc,avg_monthly_events desc,u.user_id;