首页 > 试题广场 >

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

[编程题]电商平台需要对商家的销售业绩、退款情况和客户满意度进行综合评
  • 热度指数：167 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解
【背景】：电商平台需要对商家的销售业绩、退款情况和客户满意度进行综合评估，以确定优秀商家和需要改进的商家。
【原始表】：
merchants_underline （商家）表:
  • merchant_id (商家 ID): 商家的唯一标识符，INT
  • merchant_name (商家名称): 商家的名称，VARCHAR(50)
  • industry (行业): 商家所属的行业，VARCHAR(20)
sales_underline （销售）表:
  • sale_id (销售 ID): 销售记录的唯一标识符，INT
  • merchant_id (商家 ID): 关联商家表的商家 ID，INT
  • sale_amount (销售金额): 销售的金额，DECIMAL(10, 2)
refunds_underline （退款）表:
  • refund_id (退款 ID): 退款记录的唯一标识符，INT
  • merchant_id (商家 ID): 关联商家表的商家 ID，INT
  • refund_amount (退款金额): 退款的金额，DECIMAL(10, 2)
satisfaction_underline （满意度）表:
  • satisfaction_id (满意度 ID): 满意度记录的唯一标识符，INT
  • merchant_id (商家 ID): 关联商家表的商家 ID，INT
  • satisfaction_score (满意度得分): 客户对商家的满意度得分，INT（1 - 100 分）
【要求】：根据上述表格，查询出商家的总销售金额、总退款金额、平均满意度得分。查询结果按照商家ID升序排列。要求查询出来的表格的字段如下：
  • merchant_id: 商家 ID。
  • merchant_name ：商家名称。
  • total_sales_amount: 总销售金额。
  • total_refund_amount: 总退款金额。
  • average_satisfaction_score: 平均满意度得分。（round保留2位小数）
【示例】
merchants_underline （商家）表:
sales_underline （销售）表:
refunds_underline （退款）表:
satisfaction_underline （满意度）表:
【按要求查出来的表】
【解释】
上述示例中merchant_id是1的商家名称是商家A，总销售额是5000+4000 = 9000；退款总金额是1000，满意度得分是（80+70）/2 = 75
示例1

输入

DROP TABLE IF EXISTS merchants_underline;
DROP TABLE IF EXISTS sales_underline;
DROP TABLE IF EXISTS refunds_underline;
DROP TABLE IF EXISTS satisfaction_underline;
-- 创建表
CREATE TABLE merchants_underline (
    merchant_id INT PRIMARY KEY,
    merchant_name VARCHAR(50),
    industry VARCHAR(20)
);

CREATE TABLE sales_underline (
    sale_id INT PRIMARY KEY,
    merchant_id INT,
    sale_amount DECIMAL(10, 2)
);

CREATE TABLE refunds_underline (
    refund_id INT PRIMARY KEY,
    merchant_id INT,
    refund_amount DECIMAL(10, 2)
);

CREATE TABLE satisfaction_underline (
    satisfaction_id INT PRIMARY KEY,
    merchant_id INT,
    satisfaction_score INT
);

-- 插入数据
INSERT INTO merchants_underline (merchant_id, merchant_name, industry)
VALUES (1, '商家 A', '服装'),
       (2, '商家 B', '电子产品');

INSERT INTO sales_underline (sale_id, merchant_id, sale_amount)
VALUES (1, 1, 5000.00),
       (2, 2, 8000.00),
       (3, 1, 4000.00),
       (4, 2, 6000.00);

INSERT INTO refunds_underline (refund_id, merchant_id, refund_amount)
VALUES (1, 1, 1000.00),
       (2, 2, 1500.00);

INSERT INTO satisfaction_underline (satisfaction_id, merchant_id, satisfaction_score)
VALUES (1, 1, 80),
       (2, 2, 90),
       (3, 1, 70),
       (4, 2, 60);


select * from merchants_underline;
select * from sales_underline;
select * from refunds_underline;
select * from satisfaction_underline;

输出

merchant_id|merchant_name|total_sales_amount|total_refund_amount|average_satisfaction_score
1|商家 A|9000.00|1000.00|75.00
2|商家 B|14000.00|1500.00|75.00
算法知识视频讲解
添加回答
牛客527342094号头像 牛客527342094号
SELECT A.merchant_id,
       merchant_name,
       total_sales_amount,
       total_refund_amount,
       average_satisfaction_score
FROM (SELECT MU.merchant_id,
             merchant_name,
             ROUND(SUM(sale_amount),2) AS total_sales_amount
       FROM merchants_underline AS MU
       JOIN sales_underline AS SU ON SU.merchant_id = MU.merchant_id
       JOIN refunds_underline AS RU ON RU.merchant_id = MU.merchant_id
       GROUP BY MU.merchant_id
) AS A
JOIN (SELECT MU.merchant_id,
             ROUND(SUM(refund_amount),2) AS total_refund_amount
      FROM merchants_underline AS MU
      JOIN refunds_underline AS RU ON RU.merchant_id = MU.merchant_id
       GROUP BY MU.merchant_id
             ) AS B ON B.merchant_id = A.merchant_id
JOIN (SELECT MU.merchant_id,
             ROUND(AVG(satisfaction_score),2) AS average_satisfaction_score
      FROM satisfaction_underline AS SAU
      JOIN merchants_underline AS MU ON SAU.merchant_id = MU.merchant_id  
      GROUP BY MU.merchant_id    
) AS C ON C.merchant_id = A.merchant_id
ORDER BY A.merchant_id;
发表于 2025-06-17 19:43:43 回复(0)
牛客815063974号头像 牛客815063974号
不知道哪里错了，各位帮看看
with t1
as(
    select
    a.merchant_id,
    merchant_name,
    sum(sale_amount) as total_sales_amount
    from sales_underline a
    left join merchants_underline b
    on a.merchant_id = b.merchant_id
    group by 1,2
),
t2
as(
    select
    merchant_id,
    avg(satisfaction_score) as avergae_satisfaction_score
    from satisfaction_underline
    group by 1
),
t3
as(
    select
    merchant_id,
    sum(refund_amount) as total_refund_amount
    from refunds_underline
    group by 1
)

select
t1.merchant_id,
merchant_name,
total_sales_amount,
total_refund_amount,
avergae_satisfaction_score
from t1
left join t2
on t1.merchant_id = t2.merchant_id
left join t3
on t1.merchant_id = t3.merchant_id
order by 1
发表于 2025-06-17 17:35:16 回复(0)
牛客800609510号头像 牛客800609510号
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,round(avg(satisfaction_score) ,2) as average_satisfaction_score
from satisfaction_underline
group by merchant_id
)
select merchant_id,merchant_name,total_sales_amount,refund_amount as total_refund_amount,average_satisfaction_score
from merchants_underline
join refunds_underline using(merchant_id)
join t1 using(merchant_id)
join t2 using(merchant_id)

发表于 2025-06-15 00:55:43 回复(0)
提交观点

问题信息

难度：
3条回答 14浏览

热门推荐

相关试题

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

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号