with t as (
select campaign_id,campaign_name,
sum(if(date_format(event_time,'%Y-%m') = '2023-02' and event_type = 'signup',1,0)) as conv_2023_02,
sum(if(date_format(event_time,'%Y-%m') = '2024-02' and event_type = 'signup',1,0)) as conv_2024_02,
sum(if(date_format(event_time,'%Y-%m') = '2024-01' and event_type = 'signup',1,0)) as conv_2024_01,
sum(if(date_format(event_time,'%Y-%m') = '2024-02' and event_type = 'signup',1,0)) - sum(if(date_format(event_time,'%Y-%m') = '2023-02' and event_type = 'signup',1,0)) as yoy_delta,
sum(if(date_format(event_time,'%Y-%m') = '2024-02' and event_type = 'signup',1,0)) - sum(if(date_format(event_time,'%Y-%m') = '2024-01' and event_type = 'signup',1,0)) as mom_delta,
round(ifnull(sum(if(year(event_time) = 2023 and event_type = 'signup' and region = '华北',1,0))/sum(if(year(event_time) = 2023 and event_type = 'signup',1,0)),0)*100,2) as north_pct_2023,
round(ifnull(sum(if(year(event_time) = 2023 and event_type = 'signup' and region = '华南',1,0))/sum(if(year(event_time) = 2023 and event_type = 'signup',1,0)),0)*100,2) as south_pct_2023,
round(ifnull(sum(if(year(event_time) = 2023 and event_type = 'signup' and region = '华东',1,0))/sum(if(year(event_time) = 2023 and event_type = 'signup',1,0)),0)*100,2) as east_pct_2023
 from Campaigns
join Events using(campaign_id)
join Users using(user_id)
group by campaign_id),

t1 as (
select campaign_id,round(avg(timestampdiff(minute,上一次动作时间,event_time)),2) as avg_click_to_signup_min_2024_02
from
(
select campaign_id,user_id,
event_type,event_time,lag(event_type)over(partition by campaign_id,user_id) as 上一次动作, lag(event_time)over(partition by campaign_id,user_id) as 上一次动作时间 from Events
where date_format(event_time,'%Y-%m') = '2024-02') a
where event_type = 'signup' and 上一次动作 = 'click'
group by campaign_id
),

t2 as
(
select campaign_id,channel as top_channel_2024_02 from
(
select campaign_id,channel,rank()over(partition by campaign_id order by 用户注册数 desc,字典 asc) as 排名
from
(
select campaign_id,channel,count(channel) as 用户注册数,
case 
when channel = 'app' then 1
when channel = 'mini' then 2
when channel = 'web' then 3
end as 字典
 from Events
where event_type = 'signup' and date_format(event_time,'%Y-%m') = '2024-02'
group by campaign_id,channel
) a1) a2
where 排名 = 1)

select campaign_id,campaign_name,conv_2023_02,conv_2024_02,conv_2024_01,yoy_delta,mom_delta,north_pct_2023,south_pct_2023,east_pct_2023,avg_click_to_signup_min_2024_02,top_channel_2024_02 from t
join t1 using(campaign_id)
join t2 using(campaign_id)
order by campaign_id