#处理基础表
with e as (
    select event_id,user_id,campaign_id,event_type,channel,event_time,date_format(event_time,'%Y%m') d_f,year(event_time) d_y 
    from Events
    where event_type = 'signup'
    or event_type = 'click'),
#利用lag窗口函数求出用户上一次的行为l_ty和上一次的行为时间l_tm
a as(
    select user_id,campaign_id,d_f,event_type,event_time,lag(event_type) over(partition by user_id,campaign_id order by event_time asc) l_ty,lag(event_time) over(partition by user_id,campaign_id order by event_time asc) l_tm
    from e 

),
#利用筛选，求出最近一次不晚于注册时间的 click”到“signup”的平均分钟数
b as(
    select campaign_id,round(avg(timestampdiff(second,l_tm,event_time))/60,2) d_avg
    from a 
    where event_type='signup'
    and l_ty='click'
    and d_f=202402
    group by campaign_id),
#求出注册数最多的渠道，channel数值类型是枚举，没有办法进行（app < mini < web）排序，利用cast强制转换为char进行排序
d as (
    select e.campaign_id,channel,count(distinct e.user_id) c_d,rank() over(partition by e.campaign_id order by count(distinct e.user_id) desc,cast(channel as char) asc ) r_c
    from e 
    inner join Users on e.user_id=Users.user_id
    where d_f=202402 and event_type = 'signup'
    group by e.campaign_id,channel
)

#最后整合
select e.campaign_id,
    campaign_name,
    sum(if(d_f=202302 and event_type='signup',1,0)) conv_2023_02,
    sum(if(d_f=202402 and event_type='signup',1,0)) conv_2024_02,
    sum(if(d_f=202401 and event_type='signup',1,0)) conv_2024_01,
    sum(if(d_f=202402 and event_type='signup',1,0))-sum(if(d_f=202302 and event_type='signup',1,0)) yoy_delta,
    sum(if(d_f=202402 and event_type='signup',1,0))-sum(if(d_f=202401 and event_type='signup',1,0)) mom_delta,
    ifnull(round(sum(if(d_y=2023 and event_type='signup' and region='华北',1,0))/sum(if(d_y=2023 and event_type='signup',1,0))*100,2),0) north_pct_2023,
    ifnull(round(sum(if(d_y=2023 and event_type='signup' and region='华南',1,0))/sum(if(d_y=2023 and event_type='signup',1,0))*100,2),0) south_pct_2023,
    ifnull(round(sum(if(d_y=2023 and event_type='signup' and region='华东',1,0))/sum(if(d_y=2023 and event_type='signup',1,0))*100,2),0) east_pct_2023,
    max(d_avg) avg_click_to_signup_min_2024_02,
    max(d.channel) top_channel_2024_02
from e 
inner join Campaigns c on e.campaign_id=c.campaign_id
inner join Users u on u.user_id=e.user_id
inner join b on b.campaign_id=e.campaign_id
inner join d on d.campaign_id=e.campaign_id and r_c=1
group by e.campaign_id,campaign_name
order by e.campaign_id asc ,campaign_name asc;