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

select 
    tmp2.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 
    (
        select 
            o.product_id,
            product_name,
            sum(total_sales) as total_sales_amount_of_product
        from
            oppo_products_detail as o
            left join
            (
                select
                    product_id,
                    quarter_1_sales_amount + quarter_2_sales_amount + quarter_3_sales_amount + quarter_4_sales_amount as total_sales
                from sales_info
            ) as tmp on o.product_id = tmp.product_id
        group by o.product_id
    ) as tmp2
    left join competitor_analysis as c on tmp2.product_id = c.product_id