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

with sl as(
select merchant_id
, sum(sale_amount) total_sales_amount
from sales_underline
group by 1
)

, avg as(
select merchant_id
,round(avg(satisfaction_score),2) average_satisfaction_score
from satisfaction_underline 
group by 1
)

select m.merchant_id
, m.merchant_name
, total_sales_amount
, sum(refund_amount) total_refund_amount
, average_satisfaction_score
from merchants_underline m
left join sl on m.merchant_id = sl.merchant_id
left join refunds_underline r on m.merchant_id = r.merchant_id
left join avg on m.merchant_id = avg.merchant_id
group by 1,2,3,5