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

# users、user_events
# user_id
# 2025年上半年（2025-01-01至2025-06-30）注册的'Pro'计划用户
# 在2025年第一季度（2025-01-01至2025-03-31）至少有一次'login'行为的用户
# 活跃度
with login_user as (
select user_id,
       sum(case when event_type = 'login' then 1 else 0 end) as atleast_1_login,
       # 3\在2025年第一季度的月均事件数
       ROUND(count(event_type) / 3,2) AS avg_monthly_events
from user_events
where user_id in (
                # 1\2025年上半年（2025-01-01至2025-06-30）注册的'Pro'计划用户
                select user_id
                from users
                where (registration_date between '2025-01-01' and '2025-06-30') and plan_type = 'Pro') 
      and date(event_timestamp) between '2025-01-01' and '2025-03-31'
group by user_id
        # 2\在2025年第一季度（2025-01-01至2025-03-31）至少有一次'login'行为的用户
having sum(case when event_type = 'login' then 1 else 0 end) >= 1),
score AS (
select user_id,
       # 4\用户的总活跃积分
       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)
       # 5\ 拼接
select concat(u.user_name,'(',l.user_id,')') as user_profile,
       s.total_activity_score,
       l.avg_monthly_events
FROM login_user l 
join score s on l.user_id =s.user_id
join users u on l.user_id =u.user_id
order by s.total_activity_score desc,l.avg_monthly_events desc,l.user_id asc