-- 用 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;