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

with t1 as
(
    select
        m.merchant_id,
        m.merchant_name,
        sum(s.sale_amount) total_sales_amount
    from
        merchants_underline m 
    join 
        sales_underline s 
    on 
        m.merchant_id = s.merchant_id
    group by
        m.merchant_id,
        m.merchant_name
),
t2 as
(
    select
        m.merchant_id,
        m.merchant_name,
        sum(r.refund_amount) total_refund_amount
    from
        merchants_underline m 
    join 
        refunds_underline r 
    on
        m.merchant_id = r.merchant_id 
    group by
        m.merchant_id,
        m.merchant_name  
),
t3 as
(
    select
        m.merchant_id,
        m.merchant_name,
        round(avg(s1.satisfaction_score), 2) average_satisfaction_score
    from
        merchants_underline m 
    join    
        satisfaction_underline s1
    on 
        m.merchant_id = s1.merchant_id 
    group by
        m.merchant_id,
        m.merchant_name
)
select
    t1.merchant_id,
    t1.merchant_name,
    total_sales_amount,
    total_refund_amount,
    average_satisfaction_score
from
    t1
join 
    t2
on 
    t1.merchant_id = t2.merchant_id
join 
    t3 
on 
    t1.merchant_id = t3.merchant_id
order by    
    t1.merchant_id