题解 | 电商平台需要对商家的销售业绩、退款情况和客户满意度进行综合评估

-- 逻辑拆解：关键是要连接多个表
-- 先去聚合，求出每个商家的退款和销售综合，以及平均评分（躲避笛卡尔积陷阱）
WITH total_sale AS(
    SELECT 
    merchant_id,
    SUM(sale_amount) total_sales_amount
    FROM sales_underline
    GROUP BY merchant_id
),
total_refund AS(
    SELECT
    merchant_id,
    SUM(refund_amount) total_refund_amount
    FROM refunds_underline
    GROUP BY merchant_id
),
avg_score AS(
    SELECT 
    merchant_id,
    ROUND(AVG(satisfaction_score),2) average_satisfaction_score
    FROM satisfaction_underline
    GROUP BY merchant_id

)
SELECT 
t1.merchant_id,
t1.merchant_name,
t2.total_sales_amount,
t3.total_refund_amount,
t4.average_satisfaction_score
FROM merchants_underline t1
LEFT JOIN total_sale t2
ON t1.merchant_id = t2.merchant_id
LEFT JOIN total_refund t3
ON t1.merchant_id = t3.merchant_id
LEFT JOIN avg_score t4
ON t1.merchant_id = t4.merchant_id
ORDER BY t1.merchant_id