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

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
    (
        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
            JOIN oppo_products_detail USING (product_id)
        GROUP BY
            product_id,
            product_name
    ) AS t1
    JOIN (
        SELECT
            product_id,
            competitor_name,
            total_competitor_sales_amount_2023
        FROM
            oppo_products_detail
            JOIN competitor_analysis USING (product_id)
    ) AS t2 USING (product_id)
ORDER BY
    product_id ASC