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

with t1 as(select merchants_underline.merchant_id as merchant_id,merchant_name,sum(sale_amount) as total_sales_amount from merchants_underline
join sales_underline
on merchants_underline.merchant_id = sales_underline.merchant_id
group by merchants_underline.merchant_id),

t2 as (
select merchant_id,sum(refund_amount) as total_refund_amount from refunds_underline
group by merchant_id
),

t3 as (
select t1.merchant_id as merchant_id,merchant_name,total_sales_amount,total_refund_amount from t1
join t2 
on t1.merchant_id = t2.merchant_id
),

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

select t3.merchant_id,merchant_name,total_sales_amount,total_refund_amount,average_satisfaction_score from t3
join t4
on t3.merchant_id = t4.merchant_id
order by t3.merchant_id