with user_base as (
    -- 基础用户：Pro用户 + Q1有登录
    select distinct 
        u.user_id,
        u.user_name
    from users u
    inner join user_events ue on u.user_id = ue.user_id
    where u.registration_date between '2025-01-01' and '2025-06-30'
      and u.plan_type = 'Pro'
      and date(ue.event_timestamp) between '2025-01-01' and '2025-03-31'
      and ue.event_type = 'login'
)

select
    concat(ub.user_name, '(', ub.user_id, ')') as user_profile,
    -- 总活动分数（所有事件）
    sum(
        case when ue.event_type = 'create_task' then 5
             when ue.event_type = 'export_report' then 10
             when ue.event_type = 'invite_member' then 8
             else 1
        end
    ) as total_activity_score,
    -- 第一季度月均事件数
    round(
        sum(case when date(ue.event_timestamp) between '2025-01-01' and '2025-03-31' then 1 else 0 end) / 3, 
        2
    ) as avg_monthly_events
from user_base ub
inner join user_events ue on ub.user_id = ue.user_id
group by ub.user_id, ub.user_name
order by total_activity_score desc, 
         avg_monthly_events desc, 
         ub.user_id asc
;


# # 1、筛选特定注册人群
# with t1 as (
#     select
#     user_id,
#     user_name
#     from users
#     where registration_date between '2025-01-01' and '2025-06-30'
#     and plan_type = 'Pro'
# ),
# # 2、筛选Q1至少登录一次
# t2 as (
#     select
#     distinct user_id
#     from user_events
#     where date(event_timestamp) between '2025-01-01' and '2025-03-31'
#     and event_type = 'login'
# ),
# # 3、对事件进行打分
# t3 as (
#     select
#     ue.user_id,
#     t1.user_name,
#     event_type,
#     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 score,
#     event_timestamp
#     from user_events ue
#     inner join t1 on ue.user_id = t1.user_id
#     inner join t2 on ue.user_id = t2.user_id
#     # where date(event_timestamp) between '2025-01-01' and '2025-03-31'
# ),
# # 4、计算一季度月均事件数
# t4 as (
#     select 
#     user_id,
#     round(count(event_type)/3, 2) as avg_monthly_events
#     from t3
#     where date(event_timestamp) between '2025-01-01' and '2025-03-31'
#     group by user_id
# ),
# # 5、计算总活跃分数
# t5 as (
#     select
#     user_id,
#     user_name,
#     sum(score) as total_activity_score
#     from t3
#     group by user_id, user_name
# )
# # 6、合并
# select
# concat(t5.user_name, '(', t5.user_id, ')' ) as user_profile,
# t5.total_activity_score,
# t4.avg_monthly_events
# from t5
# left join t4 on t5.user_id = t4.user_id
# order by t5.total_activity_score desc, 
#          t4.avg_monthly_events desc, 
#          t5.user_id asc;
# ;