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

WITH
t1 AS (     -- 计算商家总销售额
    SELECT
        merchant_id,
        SUM(sale_amount) AS total_sales_amount
    FROM sales_underline
    GROUP BY merchant_id
),

t2 AS (     -- 计算商家总退款额
    SELECT      
        merchant_id,
        SUM(refund_amount) total_refund_amount
    FROM refunds_underline
    GROUP BY merchant_id
),

t3 AS (     -- 计算商家满意度
    SELECT
        merchant_id,
        AVG(satisfaction_score) AS average_satisfaction_score
    FROM satisfaction_underline
    GROUP BY merchant_id
)

SELECT      -- 表连接，汇总所有指标并且排序
    a.merchant_id,
    a.merchant_name,
    t1.total_sales_amount,
    t2.total_refund_amount,
    ROUND(t3.average_satisfaction_score,2) AS average_satisfaction_score
FROM t1
INNER JOIN t2
    ON t1.merchant_id = t2.merchant_id
INNER JOIN t3
    ON t1.merchant_id = t3.merchant_id
RIGHT JOIN merchants_underline AS a
    ON t1.merchant_id = a.merchant_id
ORDER BY t1.merchant_id;