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

# 查询出商家的总销售金额、总退款金额、平均满意度得分。查询结果按照商家ID升序排列。要求查询出来的表格的字段如下：
# merchant_id: 商家 ID。
# merchant_name ：商家名称。
# total_sales_amount: 总销售金额。
# total_refund_amount: 总退款金额。
# average_satisfaction_score: 平均满意度得分。（round保留2位小数）
# 第二版
select m.merchant_id merchant_id
,m.merchant_name merchant_name
,total_sales_amount
,total_refund_amount
,average_satisfaction_score
from merchants_underline m
left join (
    select merchant_id
    ,sum(sale_amount) total_sales_amount
    from sales_underline
    group by merchant_id
) s
on m.merchant_id=s.merchant_id

left join (
    select merchant_id
    ,sum(refund_amount) total_refund_amount
    from refunds_underline
    group by merchant_id
) r
on m.merchant_id=r.merchant_id


left join (
    select merchant_id
    ,round(avg(satisfaction_score),2) average_satisfaction_score
    from satisfaction_underline
    group by merchant_id
) sa
on m.merchant_id=sa.merchant_id

# 第一次：多表join引发的笛卡尔积问题
# select m.merchant_id merchant_id
# ,merchant_name
# ,sum(sale_amount) total_sales_amount
# ,sum(refund_amount) total_refund_amount
# ,round(sum(satisfaction_score)/count(*),2) average_satisfaction_score

# from merchants_underline m
# left join sales_underline s
# on m.merchant_id=s.merchant_id

# left join refunds_underline r
# on s.merchant_id=r.merchant_id

# left join satisfaction_underline sa
# on s.merchant_id =sa.merchant_id

# group by m.merchant_id,merchant_name
# order by m.merchant_id;