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

WITH ts AS (
    SELECT
        merchant_id,
        SUM(sale_amount) total_sales_amount
    FROM sales_underline
    GROUP BY merchant_id
),
tr AS (
    SELECT
        merchant_id,
        SUM(refund_amount) total_refund_amount
    FROM refunds_underline
    GROUP BY merchant_id
),
as1 AS (
    SELECT
        merchant_id,
        ROUND(AVG(satisfaction_score),2) average_satisfaction_score
    FROM satisfaction_underline
    GROUP BY merchant_id
)
SELECT 
    mu.merchant_id,
    mu.merchant_name,
    total_sales_amount,
    total_refund_amount,
    average_satisfaction_score
FROM merchants_underline mu
JOIN ts ON ts.merchant_id = mu.merchant_id
JOIN tr ON tr.merchant_id = mu.merchant_id
JOIN as1 ON as1.merchant_id = mu.merchant_id
ORDER BY mu.merchant_id