--出题的人太缺德了，我真都后两问没明白啥意思，能说点普通话吗？

with tiaojian as (
select 
e.campaign_id,
cs.campaign_name,
count(distinct case when date_format(event_time,"%Y%m")=202302 and event_type='signup' then event_id end) as conv_2023_02,
count(distinct case when date_format(event_time,"%Y%m")=202402 and event_type='signup' then event_id end) as conv_2024_02,
count(distinct case when date_format(event_time,"%Y%m")=202401 and event_type='signup' then event_id end) as conv_2024_01,
count(distinct case when region='华北' and year(event_time)=2023 then event_id end)/count(case when year(event_time)=2023 then event_id end ) as north_pct_2023,
count(distinct case when region='华南' and year(event_time)=2023 then event_id end)/count(case when year(event_time)=2023 then event_id end ) as south_pct_2023,
count(distinct case when region='华东' and year(event_time)=2023 then event_id end)/count(case when year(event_time)=2023 then event_id end )  as east_pct_2023
from Events e inner join Campaigns cs 
on e.campaign_id=cs.campaign_id
inner join Users u  on u.user_id=e.user_id
group by e.campaign_id
),tiaojian1 as (
select 
campaign_id,
avg(timestampdiff(minute,event_time,dt)) as py
from(
select 
campaign_id,
event_time,
event_type,
lead(event_time)over(partition by campaign_id,user_id order by event_time ) as dt
from  Events
where
date_format(event_time,"%Y%m")=202402
)  as t 
where
t.event_type='click'
group by campaign_id
),tiaojian2 as (
select 
t.campaign_id,
t.channel as top_channel_2024_02
from(
select 
e.campaign_id,
channel,
dense_rank()over(partition by e.campaign_id order by count(e.channel) desc,case when channel='app' then 1 when channel='mini' then 2 when channel='web' then 3 end) as rk
from Events e inner join Users u 
on e.user_id=u.user_id
where
date_format(event_time,'%Y%m')=202402
and event_type-'signup'
group by e.campaign_id,channel
) as t 
where
t.rk=1
)

select 
t.campaign_id,
t.campaign_name,
t.conv_2023_02,
t.conv_2024_02,
t.conv_2024_01,
conv_2024_02-conv_2023_02  as yoy_delta,
conv_2024_02-conv_2024_01 as mom_delta,
ifnull(
round(
t.north_pct_2023*100,2),0) as north_pct_2023,
ifnull(
round(
t.south_pct_2023*100,2),0) as south_pct_2023,
ifnull(
round(
t.east_pct_2023*100,2),0) as east_pct_2023,
round(
t1.py,2) as avg_click_to_signup_min_2024_02,
t2.top_channel_2024_02
from tiaojian t inner join tiaojian1 t1 on t.campaign_id=t1.campaign_id
                inner join tiaojian2 t2 on t.campaign_id=t2.campaign_id