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

with  t1 as (

    select distinct merchant_id ,sum(sale_amount) over (partition by merchant_id) as total_sales_amount
    from sales_underline
),
       t2 as (
            select distinct  merchant_id,sum(refund_amount) over (partition by merchant_id) as total_refund_amount
            from refunds_underline
        ),
    t3 as (
        select distinct  merchant_id ,round((avg(satisfaction_score) over (partition by merchant_id) ),2) as average_satisfaction_score

        from satisfaction_underline
    )

select merchants_underline.merchant_id,merchant_name,t1.total_sales_amount,t2.total_refund_amount,t3.average_satisfaction_score
from merchants_underline
 join t1 on t1.merchant_id=merchants_underline.merchant_id
 join t2 on t2.merchant_id=merchants_underline.merchant_id
join t3 on t3.merchant_id=merchants_underline.merchant_id