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

--简单拆解方法,为了避免，出现多个竞品的总金额，导致计算逻辑错误
--先算各种年总销售量
--在用上述的表连接竞品表在求，差额
with tiaojian as (
 select 
ol.product_id,
ol.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 oppo_products_detail ol inner join 
 sales_info sf on ol.product_id=sf.product_id
 group by ol.product_id
)

select 
t.product_id,
t.product_name,
cs.competitor_name,
t.total_sales_amount_of_product,
t.total_sales_amount_of_product-cs.total_competitor_sales_amount_2023 as sales_difference_with_competitor
from tiaojian t inner join competitor_analysis cs 
on t.product_id=cs.product_id
order by t.product_id asc