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

with temp0 as (
    select distinct u.user_id
    from users u inner join user_events ue
    on u.user_id = ue.user_id
    where plan_type = "Pro" and date_format(registration_date,"%Y-%m-%d") >= "2025-01-01"
    and date_format(registration_date,"%Y-%m-%d") <= "2025-06-30"
    and event_type = "login" and date_format(event_timestamp,"%Y-%m-%d") >= "2025-01-01"
    and date_format(event_timestamp,"%Y-%m-%d") <= "2025-03-31"
), temp1 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 ue
    group by user_id
), temp2 as (
    select ue.user_id,round(count(*)/3,2) as avg_monthly_events
    from user_events ue
    where date_format(event_timestamp,"%Y-%m") >= "2025-01"
    and date_format(event_timestamp,"%Y-%m") <= "2025-03"
    group by ue.user_id
)


select concat(user_name,"(",u.user_id,")") as user_profile,total_activity_score,
avg_monthly_events
from temp0 t0 inner join temp1 t1 on t0.user_id = t1.user_id
inner join temp2 t2 on t2.user_id = t1.user_id
inner join users u on u.user_id = t0.user_id
order by total_activity_score desc,avg_monthly_events desc,t0.user_id asc;