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

WITH oppototal AS (
    SELECT
        opd.product_id,
        opd.product_name,
        SUM(si.quarter_1_sales_amount+si.quarter_2_sales_amount+si.quarter_3_sales_amount+si.quarter_4_sales_amount) AS total_sales_amount_of_product
    FROM
        oppo_products_detail AS opd 
        INNER JOIN
        sales_info AS si ON opd.product_id=si.product_id
    GROUP BY
        opd.product_id,
        opd.product_name
)
SELECT
    ot.product_id,
    ot.product_name,
    ca.competitor_name,
    ot.total_sales_amount_of_product,
    ot.total_sales_amount_of_product-ca.total_competitor_sales_amount_2023 AS sales_difference_with_competitor
FROM
    oppototal AS ot 
    INNER JOIN 
    competitor_analysis AS ca ON ot.product_id=ca.product_id
ORDER BY
    ot.product_id ASC;