--您在里面看呢，这解法简单，分解解法
--1.筛选题干的条件，2025年上半年注册的,PRO的用户
with tiaojian1 as (
select 
user_id,
user_name
from users
where
date(registration_date) between '2025-01-01' and '2025-06-30'
and
plan_type='Pro'
),
--2.Q1至少登录过一次用户
tiaojian2 as (
select 
t1.user_id,
t1.user_name
from tiaojian1 t1 inner join user_events u on t1.user_id=u.user_id
where
year(event_timestamp)=2025
and month(event_timestamp) in (1,2,3)
and event_type='login'
group by t1.user_id,t1.user_name
having count(event_id)>=1
)
--3.筛选出后连接，求结果
select 
concat(
t2.user_name,'(',t2.user_id,')') as user_profile,
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,
round(
count(case when date(event_timestamp) between '2025-01-01' and '2025-03-31' then event_id end)/3,2) as avg_monthly_events
from tiaojian2 t2 inner join user_events u2 on t2.user_id=u2.user_id
group by t2.user_name,t2.user_id
order by  total_activity_score desc ,avg_monthly_events desc,t2.user_id asc