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

WITH t1 AS(
    SELECT merchant_id, SUM(sale_amount) AS total_sales_amount
    FROM sales_underline
    GROUP BY merchant_id
),
t2 AS(
    SELECT merchant_id, SUM(refund_amount) AS total_refund_amount
    FROM refunds_underline
    GROUP BY merchant_id
),
t3 AS(
    SELECT merchant_id, ROUND(AVG(satisfaction_score), 2) AS average_satisfaction_score
    FROM satisfaction_underline
    GROUP BY merchant_id
)
SELECT
    mu.merchant_id,
    merchant_name,
    total_sales_amount,
    total_refund_amount,
    average_satisfaction_score
FROM merchants_underline mu
JOIN t1 ON(mu.merchant_id = t1.merchant_id)
JOIN t2 ON(mu.merchant_id = t2.merchant_id)
JOIN t3 ON(mu.merchant_id = t3.merchant_id)
ORDER BY mu.merchant_id

with total_sales_amount_tb as(
    select
        su.merchant_id
        , mu.merchant_name
        , round(sum(su.sale_amount), 2) as total_sales_amount
    from
        sales_underline as su
        left join merchants_underline as mu on su.merchant_id = mu.merchant_id
    group by
        su.merchant_id
        , mu.merchant_name
)
, total_refund_amount_tb as(
    select
        ru.merchant_id
        , mu.merchant_name
        , round(sum(ru.refund_amount), 2) as total_refund_amount
    from
        refunds_underline as ru
        left join merchants_underline as mu on ru.merchant_id = mu.merchant_id
    group by
        ru.merchant_id
        , mu.merchant_name
)
, average_satisfaction_score_tb as(
    select
        su.merchant_id
        , mu.merchant_name
        , round(avg(su.satisfaction_score), 2) as average_satisfaction_score
    from
        satisfaction_underline as su
        left join merchants_underline as mu on su.merchant_id = mu.merchant_id
    group by
        su.merchant_id
        , mu.merchant_name
)

select
    mu.merchant_id
    , mu.merchant_name
    , tsa_tb.total_sales_amount
    , tra_tb.total_refund_amount
    , ass_tb.average_satisfaction_score
from
    merchants_underline as mu
    left join total_sales_amount_tb as tsa_tb on mu.merchant_id = tsa_tb.merchant_id
    left join total_refund_amount_tb as tra_tb on mu.merchant_id = tra_tb.merchant_id
    left join average_satisfaction_score_tb as ass_tb on mu.merchant_id = ass_tb.merchant_id
order by
    mu.merchant_id

with
    k as (
        select
            a.merchant_id,
            merchant_name,
            round(sum(sale_amount), 2) total_sales_amount
        from
            merchants_underline a
            left join sales_underline b on a.merchant_id = b.merchant_id
        group by
            a.merchant_id,
            merchant_name
    ),
    k1 as (
        select
            k.merchant_id,
            merchant_name,
            total_sales_amount,
            round(sum(refund_amount), 2) total_refund_amount
        from
            k
            left join refunds_underline c on k.merchant_id = c.merchant_id
        group by
            k.merchant_id,
            merchant_name
    )
select
    k1.merchant_id,
    merchant_name,
    total_sales_amount,
    total_refund_amount,
    round(avg(satisfaction_score), 2) average_satisfaction_score
from
    k1
    left join satisfaction_underline d on k1.merchant_id = d.merchant_id
group by
    k1.merchant_id,
    merchant_name

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

个人思路：

1. 将merchant_underline表与satisfaction_underline表左连接（防止出现有 merchant_id 却没有满意度评分的商家），按merchant_underline中的 merchant_id 分组，输出每个 merchant_id 的平均满意度（保留两位小数）以及对应的 merchant_name，记为 s1
2. 将refunds_underline按照 merchant_id 分组，输出每个出现在该表中的 merchant_id 的总退款金额，记为r1
3. 将sales_underline按照 merchant_id 分组，输出每个出现在该表中的 merchant_id 的总销售金额，记为s0
4. 将 s0 与 s0、r1 分别按照关键字 merchant_id 左连接
5. 输出题目对应的五个指标，并按照 s1.merchant_id 升序排列

select m.merchant_id,
       m.merchant_name,
       round(sum(s.sale_amount),2) as total_sales_amount,
       round(sum(r.refund_amount),2) as total_refund_amount,
       round(avg(u.satisfaction_score),2) as aveage_satifaction_score

from sales_underline as s
left join merchants_underline as m 
  on s.merchant_id=m.merchant_id
left join refunds_underline as r 
  on s.merchant_id=r.merchant_id
left join satisfaction_underline  as u
   on s.merchant_id=u.merchant_id

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

WITH
  temp1 AS(
    SELECT merchant_id, SUM(sale_amount) AS total_sales_amount
    FROM sales_underline
    GROUP BY merchant_id
  ),
  temp2 AS(
    SELECT merchant_id, SUM(refund_amount) AS total_refund_amount
    FROM refunds_underline
    GROUP BY merchant_id
  ),
  temp3 AS(
    SELECT 
      merchant_id, 
      ROUND(AVG(satisfaction_score), 2) AS average_satisfaction_score
    FROM satisfaction_underline 
    GROUP BY merchant_id
  )
SELECT
  mu.merchant_id,
  merchant_name,
  total_sales_amount,
  total_refund_amount,
  average_satisfaction_score
FROM merchants_underline mu
JOIN temp1 USING (merchant_id)
JOIN temp2 USING (merchant_id)
JOIN temp3 USING (merchant_id)
ORDER BY mu.merchant_id