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

# 不同地区 不同渠道下 全年销售总额，以及和主要竞品2023年全年销售总金额差值

with
t1 as(
    select
        product_id,
        sum(
            quarter_1_sales_amount
            +quarter_2_sales_amount
            +quarter_3_sales_amount
            +quarter_4_sales_amount
        ) as total_sales_amount_of_product
    from
        sales_info
    group by
        product_id
)
,
t2 as(
    select
        product_id,
        product_name,
        competitor_name,
        total_sales_amount_of_product,
        total_sales_amount_of_product-total_competitor_sales_amount_2023 as sales_difference_with_competitor
    from
        t1
        left join oppo_products_detail using(product_id)
        left join competitor_analysis using(product_id)
)

select * from t2