题解 | 内容社区用户活跃度、转化与广告归因分析

with t1 as(
    select
        campaign_id,
        campaign_name,
        case when date_format(event_time,'%Y%m') = 202302 and event_type = 'signup' then 1 end temp_conv_2023_02,
        case when date_format(event_time,'%Y%m') = 202402 and event_type = 'signup' then 1 end temp_conv_2024_02,
        case when date_format(event_time,'%Y%m') = 202401 and event_type = 'signup' then 1 end temp_conv_2024_01,
        case when year(event_time) = 2023 and region = '华北' and event_type = 'signup'
then 1 end temp_north_pct_2023,
        case when year(event_time) = 2023 and region = '华南' and event_type = 'signup'
then 1 end temp_south_pct_2023,
        case when year(event_time) = 2023 and region = '华东' and event_type = 'signup'
then 1 end temp_east_pct_2023,
        case when year(event_time) = 2023 and event_type = 'signup' then 1 end temp_overall_pct_2023,
        event_time,
        event_type,
        channel,
        user_id
    from
        Campaigns s1
    left join
        Events s2
    using(campaign_id)
    left join
        Users s3
    using(user_id)
)
,t2 as(
    select
        s1.campaign_id,
        s1.user_id,
        min(timestampdiff(minute,s2.event_time,s1.latest_signup_time)) click_to_signup_min_2024_02
    from
        (select campaign_id,user_id,max(event_time) latest_signup_time from Events where event_type = 'signup' group by 1,2) s1
    left join 
        Events s2
    on s1.campaign_id = s2.campaign_id and s1.user_id = s2.user_id and event_type = 'click' and s1.latest_signup_time >= s2.event_time
    group by 1,2
        )
,t3 as(
    select
        campaign_id,campaign_name,channel
    from
        (select campaign_id,campaign_name,channel,count(distinct case when event_type = 'signup' then user_id end)nums,rank() over(partition by campaign_id,campaign_name order by count(case when event_type = 'signup' then user_id end) desc,case when channel = 'app' then 1 when channel = 'mini' then 2 else 3 end
                 asc ) rk from t1 where date_format(event_time,'%Y%m') = 202402 group by 1,2,3)k
    where rk = 1
)
,finalt1 as(
    select
        campaign_id,
        campaign_name,
        count( temp_conv_2023_02) conv_2023_02,
        count( temp_conv_2024_02) conv_2024_02,
        count( temp_conv_2024_01) conv_2024_01,
        count( temp_conv_2024_02)-count( temp_conv_2023_02) yoy_delta,
        count( temp_conv_2024_02)-count( temp_conv_2024_01) mom_delta,
        round(coalesce(100*count( temp_north_pct_2023)/nullif(count( temp_overall_pct_2023),0),0),2) north_pct_2023,
        round(coalesce(100*count( temp_south_pct_2023)/nullif(count( temp_overall_pct_2023),0),0),2) south_pct_2023,
        round(coalesce(100*count( temp_east_pct_2023)/nullif(count( temp_overall_pct_2023),0),0),2) east_pct_2023

    from 
        t1
    group by 1,2
)

select 
    s1.*,avg_click_to_signup_min_2024_02,
    channel top_channel_2024_02
from finalt1 s1
left join
    (select campaign_id,round(avg(click_to_signup_min_2024_02),2)avg_click_to_signup_min_2024_02 from t2 group by 1) s2
using(campaign_id)
left join
    t3 s3 
using(campaign_id)

我这才是正确的基于业务理解的正解，主要是t2这块应该选择每个campaign_id下每个user_id的最后一次signup的event_time,以及对应的在这之前的最近邻一次click的event_time作为广告归因指标才对。

其他题解都是杂鱼理解！你们的题解能过完全是这个测试样例太少了，被蒙中了而已