with temp0 as (
    select campaign_id,count(*) as conv_2023_02
    from Events
    where date_format(event_time,"%Y-%m") = "2023-02" and event_type = "signup"
    group by campaign_id
), temp1 as (
    select campaign_id,count(*) as conv_2024_02
    from Events
    where date_format(event_time,"%Y-%m") = "2024-02" and event_type = "signup"
    group by campaign_id
), temp2 as (
    select campaign_id,count(*) as conv_2024_01
    from Events
    where date_format(event_time,"%Y-%m") = "2024-01" and event_type = "signup"
    group by campaign_id
), temp3 as (
    select e.campaign_id,
        round(count(if(region="华北",1,null))/count(*)*100,2) as north_pct_2023,
        round(count(if(region="华南",1,null))/count(*)*100,2) as south_pct_2023,
        round(count(if(region="华东",1,null))/count(*)*100,2) as east_pct_2023
    from Events e inner join Users u 
    on e.user_id = u.user_id
    where year(event_time) = 2023 and event_type="signup"
    group by e.campaign_id
), temp4 as (
    select campaign_id,user_id,event_time
    from Events
    where date_format(event_time,"%Y-%m") = "2024-02" and event_type in ("click")
), temp5 as (
    select campaign_id,user_id,event_time
    from Events
    where date_format(event_time,"%Y-%m") = "2024-02" and event_type in ("signup")
), temp6 as (
    select 
        t4.campaign_id,
        timestampdiff(MINUTE,t4.event_time,t5.event_time) as click_to_signup_minute
    from temp4 t4 inner join temp5 t5
    on t4.campaign_id = t5.campaign_id and t4.user_id= t5.user_id
), temp7 as (
    select campaign_id, round(sum(click_to_signup_minute)/count(*),2) as avg_click_to_signup_min_2024_02
    from temp6
    group by campaign_id
), temp8 as (
    select campaign_id,channel,
    case 
        when channel = "app" then 1
        when channel = "mini" then 2
        else 3 
    end as seq,
    count(*) as channel_count
    from Events
    where date_format(event_time,"%Y-%m") = "2024-02" and event_type = "signup"
    group by campaign_id,channel
), temp9 as (
    select campaign_id,channel,
    row_number()over(partition by campaign_id order by channel_count desc,seq asc) as rk
    from temp8
), temp10 as (
    select campaign_id,channel as top_channel_2024_02
    from temp9
    where rk = 1 
), temp11 as (
    select 
        c.campaign_id,
        ifnull(conv_2023_02,0) as conv_2023_02,
        ifnull(conv_2024_02,0) as conv_2024_02,
        ifnull(conv_2024_01,0) as conv_2024_01,
        campaign_name,
        ifnull(conv_2024_02,0) - ifnull(conv_2023_02,0) as yoy_delta,
        ifnull(conv_2024_02,0) - ifnull(conv_2024_01,0) as mom_delta,
        ifnull(north_pct_2023,0.00) as north_pct_2023,
        ifnull(south_pct_2023,0.00) as south_pct_2023,
        ifnull(east_pct_2023,0.00) as east_pct_2023
    from Campaigns c left join temp0 t0 on c.campaign_id = t0.campaign_id
    left join temp1 t1 on t1.campaign_id = c.campaign_id
    left join temp2 t2 on t2.campaign_id = c.campaign_id
    left join temp3 t3 on t3.campaign_id = c.campaign_id
), temp12 as (
    select 
        t11.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 temp11 t11 left join temp7 t7 on t11.campaign_id = t7.campaign_id
    left join temp10 t10 on t10.campaign_id = t11.campaign_id
)

select * 
from temp12 t12
order by campaign_id asc,campaign_name asc;