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

-- 用 CTE 表达式分步写很清晰
WITH total_sales AS(
    SELECT
        merchant_id,
        SUM(sale_amount) AS total_sales_amount
    FROM sales_underline
    GROUP BY merchant_id
),
total_refund AS(
    SELECT
        merchant_id,
        SUM(refund_amount) AS total_refund_amount
    FROM refunds_underline
    GROUP BY merchant_id
),
average_satisfaction_score AS(
    SELECT
        merchant_id,
        ROUND(AVG(satisfaction_score),2) AS average_satisfaction_score
    FROM satisfaction_underline
    GROUP BY merchant_id
)
SELECT
    merchant_id,
    merchant_name,
    total_sales_amount,
    total_refund_amount,
    average_satisfaction_score
FROM merchants_underline
JOIN total_sales USING(merchant_id)
JOIN total_refund USING(merchant_id)
JOIN average_satisfaction_score USING(merchant_id)
ORDER BY merchant_id ASC;