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

with
    t1 as (
        select
            product_id,
            product_name,
            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
            left join oppo_products_detail using (product_id)
        group by
            product_id,
            product_name
    )
select
    product_id,
    product_name,
    competitor_name,
    total_sales_amount_of_product,
    total_sales_amount_of_product - total_del_money as sales_difference_with_competitor
from
    t1
    left join (
        select
            product_id,
            competitor_name,
            sum(total_competitor_sales_amount_2023) as total_del_money
        from
            competitor_analysis
        group by
            product_id,
            competitor_name
    ) as t2 using (product_id)
    order by product_id asc;