题解 | 深入分析各款产品年总销售额与竞品的年度对比

with t1 as (
    select distinct sales_info.product_id,sales_channel,region,quarter_1_sales_amount,sum(quarter_1_sales_amount) over  (partition by sales_info.product_id) as t11
         ,sum(total_competitor_sales_amount_2023) over (partition by sales_info.product_id) as t12
    ,product_name,competitor_name
    from sales_info join oppo_products_detail on sales_info.product_id =oppo_products_detail.product_id
    join competitor_analysis on sales_info.product_id=competitor_analysis.product_id

), t2 as(
   select distinct sales_info.product_id,sales_channel,region,quarter_2_sales_amount,sum(quarter_2_sales_amount) over  (partition by sales_info.product_id) as t21
,product_name,competitor_name,total_competitor_sales_amount_2023 ,sum(total_competitor_sales_amount_2023) over (partition by sales_info.product_id) as t22

    from sales_info join oppo_products_detail on sales_info.product_id =oppo_products_detail.product_id
    join competitor_analysis on sales_info.product_id=competitor_analysis.product_id
    )
,t3 as (
       select distinct sales_info.product_id,sales_channel,region,quarter_3_sales_amount,sum(quarter_3_sales_amount) over  (partition by sales_info.product_id) as t31
,product_name,competitor_name,total_competitor_sales_amount_2023 ,sum(total_competitor_sales_amount_2023) over (partition by sales_info.product_id) as t32

    from sales_info join oppo_products_detail on sales_info.product_id =oppo_products_detail.product_id
    join competitor_analysis on sales_info.product_id=competitor_analysis.product_id

    ),t4 as (
    select distinct sales_info.product_id,sales_channel,region,quarter_4_sales_amount,sum(quarter_4_sales_amount) over  (partition by sales_info.product_id) as t41
,product_name,competitor_name,total_competitor_sales_amount_2023 ,sum(total_competitor_sales_amount_2023) over (partition by sales_info.product_id) as t42

    from sales_info join oppo_products_detail on sales_info.product_id =oppo_products_detail.product_id
    join competitor_analysis on sales_info.product_id=competitor_analysis.product_id

)
    ,t5 as (
        select  distinct t1 .product_id,t1.product_name,t1.competitor_name,t11+t21+t31+t41 as total_sales_amount_of_product,t2.total_competitor_sales_amount_2023
from

t1 join t2 on t1.product_id=t2.product_id
    join  t3 on t1.product_id=t3.product_id
join t4 on t4.product_id=t1.product_id
)

select product_id,product_name,competitor_name,total_sales_amount_of_product,(total_sales_amount_of_product-t5.total_competitor_sales_amount_2023) as sales_difference_with_competitor
from t5