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

with 
sales as(
    select
        merchant_id,
        sum(sale_amount) as total_sales_amount
    from sales_underline		
    group by merchant_id),
refund as(
    select
        merchant_id,
        sum(refund_amount) as total_refund_amount
    from refunds_underline			
    group by merchant_id),
score as(
    select
        merchant_id,
        round(avg(satisfaction_score),2) as average_satisfaction_score
    from satisfaction_underline				
    group by merchant_id)

select
    m.merchant_id,m.merchant_name,
    sales.total_sales_amount,
    refund.total_refund_amount,
    score.average_satisfaction_score
from merchants_underline m
left join sales on m.merchant_id=sales.merchant_id
left join refund on m.merchant_id=refund.merchant_id
left join score on m.merchant_id=score.merchant_id
order by m.merchant_id