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

WITH sales_agg AS (
    SELECT merchant_id, SUM(sale_amount) AS total_sales
    FROM sales_underline
    GROUP BY merchant_id
),
refunds_agg AS (
    SELECT merchant_id, SUM(refund_amount) AS total_refunds
    FROM refunds_underline
    GROUP BY merchant_id
),
sat_agg AS (
    SELECT merchant_id, AVG(satisfaction_score) AS avg_satisfaction
    FROM satisfaction_underline
    GROUP BY merchant_id
)
SELECT 
    mer.merchant_id,
    mer.merchant_name,
    ROUND(COALESCE(s.total_sales, 0), 2) AS total_sales_amount,
    ROUND(COALESCE(r.total_refunds, 0), 2) AS total_refund_amount,
    ROUND(COALESCE(sat.avg_satisfaction, 0), 2) AS average_satisfaction_score
FROM merchants_underline mer
LEFT JOIN sales_agg s ON mer.merchant_id = s.merchant_id
LEFT JOIN refunds_agg r ON mer.merchant_id = r.merchant_id
LEFT JOIN sat_agg sat ON mer.merchant_id = sat.merchant_id;