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

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) AS 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 t4.merchant_id,
       merchant_name,
       t1.total_sales_amount,
       t2.total_refund_amount,
       ROUND(t3.average_satisfaction_score,2) AS average_satisfaction_score
FROM merchants_underline t4 
LEFT JOIN t1 ON t4.merchant_id=t1.merchant_id
LEFT JOIN t2 ON t4.merchant_id=t2.merchant_id
LEFT JOIN t3 ON t4.merchant_id=t3.merchant_id
ORDER BY t4.merchant_id