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

select distinct t1.merchant_id
,t1.merchant_name
,s.total_sales_amount
,r.total_refund_amount
,round(t.avg_satisfaction_score,2) as average_satisfaction_score
from(
    select merchant_id
    ,merchant_name
    from merchants_underline
    group by merchant_id,merchant_name
) t1
left join (
    select merchant_id
    ,sum(sale_amount) as total_sales_amount
    from sales_underline
    group by merchant_id
) s on t1.merchant_id = s.merchant_id
left join (
    select merchant_id
    ,sum(refund_amount) as total_refund_amount
    from refunds_underline
    group by merchant_id
) r on t1.merchant_id = r.merchant_id
left join(
    select merchant_id
    ,coalesce(avg(satisfaction_score),0) as avg_satisfaction_score
    from satisfaction_underline
    group by merchant_id
) t on t1.merchant_id = t.merchant_id
order by merchant_id