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

with b 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)
select a.product_id as product_id,
    a.product_name as product_name,
    c.competitor_name as competitor_name,
    b.total_sales_amount_of_product as total_sales_amount_of_product,
    (b.total_sales_amount_of_product-c.total_competitor_sales_amount_2023) as sales_difference_with_competitor
    from oppo_products_detail a join b using(product_id)
join competitor_analysis c using(product_id)

注意：b表已经分组好了，原表a和c也是分好的组表，无需再最后还用group by语句，再次分组。