题解 | 评估2023年不同品牌商品的销售趋势和客户满意度

-- 逻辑拆解：时间条件-2023年，求值-销售额、销售数量、平均满意度评分，题目难点：销售表和反馈表中，可能存在同一对象多条记录，直接连接会产生笛卡尔积
WITH avg_c_score AS(
    SELECT sales_id,AVG(satisfaction_score) avg_score
    FROM customer_feedback
    GROUP BY sales_id
),
total_b_sales AS(
    SELECT sales_id,brand_id,SUM(sales_quantity) total_quantity,SUM(sales_amount) total_sales
    FROM sales_data
    WHERE sales_month BETWEEN '2023-01' AND '2023-12'
    GROUP BY brand_id,sales_id
)
SELECT 
    t1.brand_id,
    SUM(t1.total_sales) total_sales_amount,
    SUM(t1.total_quantity) total_sales_quantity,
    ROUND(AVG(t2.avg_score),2) avg_satisfaction_score
FROM total_b_sales t1
LEFT JOIN avg_c_score t2
ON t1.sales_id = t2.sales_id
GROUP BY t1.brand_id
ORDER BY t1.brand_id