首页 > 试题广场 >

统计每个产品的销售情况

[编程题]统计每个产品的销售情况
  • 热度指数：10479 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解
数据查询需求说明
为了对每个产品的营销进行新的策划，需要统计2023年每个产品的销售情况。现有三个原始数据表格：customers（顾客）、products（产品）和orders（订单），其结构如下：

  customers（顾客）
字段名 数据类型 说明
customer_id（顾客ID） 整数 顾客的唯一标识符
customer_name（顾客姓名） 字符串（最大长度50） 顾客的姓名
customer_email（顾客邮箱） 字符串（最大长度50） 顾客的电子邮箱地址
customer_age（顾客年龄） 整数 顾客的年龄
PRIMARY KEY (customer_id) - 将customer_id设置为主键，确保每个顾客ID的唯一性。
products（产品）
字段名 数据类型 说明
product_id（产品ID） 整数 产品的唯一标识符
product_name（产品名称） 字符串（最大长度50） 产品的名称
unit_price（单价） 十进制数（保留两位小数） 产品的单价
PRIMARY KEY (product_id) - 将product_id设置为主键，确保每个产品ID的唯一性。
orders（订单）
字段名 数据类型 说明
order_id（订单ID） 整数 订单的唯一标识符
customer_id（顾客ID） 整数 顾客的ID，对应customers表格中的customer_id
product_id（产品ID） 整数 产品的ID，对应products表格中的product_id
quantity（数量） 整数 产品的数量
order_date（订单日期） 日期 订单的日期
PRIMARY KEY (order_id) - 将order_id设置为主键，确保每个订单ID的唯一性。

查询要求

根据上述表格，查询2023年每个产品的以下信息：

  • 产品ID（product_id）：产品的ID。
  • 总销售额（total_sales）：该产品的2023年总销售额。
  • 单价（unit_price）：产品的单价。
  • 总销量（total_quantity）：该产品的2023年总销售数量。
  • 月平均销售额（avg_monthly_sales）：2023年该产品的月均销售额。
  • 单月最高销量（max_monthly_quantity）：2023年该产品的最大月销售数量。
  • 购买量最多的客户年龄段（customer_age_group）：2023年购买该产品数量最多的顾客的年龄段（1-10，11-20，21-30，31-40，41-50，51-60，61+）


排序规则

  • 按照每个产品的总销售额降序排列。
  • 如果总销售额一致，则按照产品的ID升序排列。
  • 当存在两个客户年购买量都是最高时，customer_age_group展示年龄小的顾客的年龄段。


计算说明

  • 总销售额 = 总销量 × 单价
  • 月平均销售额 = 总销售额 / 12
  • 所有计算结果保留两位小数。

【示例】
customers（顾客）表格

products（产品）表格

orders（订单）表格

按要求查询出来的结果

示例说明

假设产品104的2023年销售总量是6，单价是120.00，则：

  • 总销售额 = 6 × 120 = 720.00
  • 月平均销售额 = 720 / 12 = 60.00
  • 购买量最大的客户ID是2的Bob，年龄是30，所在年龄段是21-30。



示例1

输入

drop table if exists customers ;
drop table if exists products ;
drop table if exists orders ;
CREATE TABLE customers (
    customer_id INT,
    customer_name VARCHAR(50),
    customer_email VARCHAR(50),
    customer_age INT,
    PRIMARY KEY (customer_id)
);

INSERT INTO customers (customer_id, customer_name, customer_email, customer_age) VALUES
(1, 'Alice', 'alice@example.com', 25),
(2, 'Bob', 'bob@example.com', 30),
(3, 'Charlie', 'charlie@example.com', 22),
(4, 'David', 'david@example.com', 18),
(5, 'Eve', 'eve@example.com', 35);

CREATE TABLE products (
    product_id INT,
    product_name VARCHAR(50),
    unit_price DECIMAL(10, 2),
    PRIMARY KEY (product_id)
);

INSERT INTO products (product_id, product_name, unit_price) VALUES
(101, 'Product A', 50.00),
(102, 'Product B', 75.00),
(103, 'Product C', 100.00),
(104, 'Product D', 120.00),
(105, 'Product E', 90.00);

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    product_id INT,
    quantity INT,
    order_date DATE,
    PRIMARY KEY (order_id)
);

INSERT INTO orders (order_id, customer_id, product_id, quantity, order_date) VALUES
(1, 1, 101, 2, '2023-01-15'),
(2, 2, 102, 3, '2023-02-20'),
(3, 3, 103, 1, '2023-03-10'),
(4, 4, 104, 2, '2023-04-05'),
(5, 5, 105, 4, '2023-05-12'),
(6, 1, 102, 2, '2023-06-18'),
(7, 2, 103, 3, '2023-07-22'),
(8, 3, 104, 1, '2023-08-30'),
(9, 4, 105, 2, '2023-09-14'),
(10, 5, 101, 4, '2023-10-25'),
(11, 1, 103, 2, '2023-11-08'),
(12, 2, 104, 3, '2023-12-19');

输出

product_id|total_sales|unit_price|total_quantity|avg_monthly_sales|max_monthly_quantity|customer_age_group
104|720.00|120.00|6|60.00|3|21-30
103|600.00|100.00|6|50.00|3|21-30
105|540.00|90.00|6|45.00|4|31-40
102|375.00|75.00|5|31.25|3|21-30
101|300.00|50.00|6|25.00|4|31-40
算法知识视频讲解
添加回答
Mysql
ke1ke2头像 ke1ke2
select
    product_id,
    total_sales,
    unit_price,
    total_quantity,
    avg_monthly_sales,
    max(max_monthly_quantity) over(partition by product_id) as max_monthly_quantity,
    max(customer_age_group) over(partition by product_id) as customer_age_group
from (
    select
        product_id,
        sum(quantity) over(partition by product_id, year(order_date)) as total_quantity,
        unit_price,
        (sum(quantity) over(partition by product_id, year(order_date))) * unit_price as total_sales,
        round((sum(quantity) over(partition by product_id, year(order_date))) * unit_price / 12, 2) as avg_monthly_sales,
        max(quantity) over(partition by product_id, month(order_date)) as max_monthly_quantity,
        case
            when customer_age between 1 and 10 then '1-10'
            when customer_age between 11 and 20 then '11-20'
            when customer_age between 21 and 30 then '21-30'
            when customer_age between 31 and 40 then '31-40'
            when customer_age between 41 and 50 then '41-50'
            when customer_age between 51 and 60 then '51-60'
            else '60+'
        end as customer_age_group,
        rank() over(partition by product_id order by quantity desc, customer_age) as rk
    from (
        select
            o.product_id,
            unit_price,
            quantity,
            order_date,
            customer_age
        from orders as o
        join customers as c on o.customer_id = c.customer_id
        join products as p on o.product_id = p.product_id  
    ) as b
    where year(order_date) = 2023
) as a
where rk = 1
order by total_sales desc, product_id asc

发表于 2025-06-25 13:32:33 回复(0)
Mysql
牛客653287588号头像 牛客653287588号 
select a.*,b.customer_age_group
from (
select product_id
,sum(total_sales)total_sales
,unit_price
,sum(quantity)total_quantity
,round(sum(total_sales)/12,2) avg_monthly_sales
,max(max_sale_month) max_monthly_quantity
from (
select a.product_id
,quantity*unit_price total_sales
,unit_price
,quantity 
,sum(quantity)over(partition by month(order_date),product_id ) max_sale_month
from orders a 
left join products b 
on a.product_id = b.product_id
where year(order_date)='2023'
)t 
group by product_id,unit_price
)a 
left join (

select product_id
,case when customer_age<=10 then '1-10'
    when customer_age<=20 then '11-20'
    when customer_age<=30 then '21-30'
    when customer_age<=40 then '31-40'
    when customer_age<=50 then '41-50'
    when customer_age<=60 then '51-60'
    else '61+' end customer_age_group
,sum(quantity) quantity
,row_number()over(partition by product_id order by sum(quantity) desc ,min(customer_age_group)) rn
from orders a 
left join  customers c 
on a.customer_id = c.customer_id
where year(order_date)='2023'
group by product_id
,case when customer_age<=10 then '1-10'
    when customer_age<=20 then '11-20'
    when customer_age<=30 then '21-30'
    when customer_age<=40 then '31-40'
    when customer_age<=50 then '41-50'
    when customer_age<=60 then '51-60'
    else '61+' end
    
)b 
on a.product_id = b.product_id and b.rn = 1
order by total_sales desc ,a.product_id

发表于 2025-06-18 22:27:33 回复(0)
Mysql
牛客917810122号头像 牛客917810122号
这道题的题目有问题吧
104的customer_age_group是21-30
根据原表购买104的用户分别是2，3，4，购买量都是1，那么
年龄最小的分段不应该是11-20吗？
发表于 2025-05-10 15:00:53 回复(0)
Mysql
盘永帅头像 盘永帅
--我燃尽了，为什么这个说：您的程序输出的格式不符合要求（比如空格和换行与要求不一致）。但是结果又是对的
select
product_id,
total_sales,
unit_price,
total_quantity,
avg_monthly_sales,
max_monthly_quantity,
customer_age_group
from
        (select
        a.product_id,
        a.unit_price,
        round(sum(a.unit_price*b.quantity),2) as total_sales,
        sum(b.quantity) as total_quantity,
        round(sum(a.unit_price*b.quantity)/12,2) as avg_monthly_sales,
        c.max_monthly_quantity,
        row_number() over(partition by b.product_id order by c.max_monthly_quantity desc ) as rk,
        case
            when d.customer_age between 1 and 10 then '1 - 10'
            when d.customer_age between 11 and 20 then '11 - 20'
            when d.customer_age between 21 and 30 then '21 - 30'
            when d.customer_age between 31 and 40 then '31 - 40'
            when d.customer_age between 41 and 50 then '41 - 50'
            when d.customer_age between 51 and 60 then '51 - 60'
            else '61+'
            end as customer_age_group
        from products a
        join orders b
        on a.product_id=b.product_id
            join (
                select
                o.product_id,
                max(o.quantity) as max_monthly_quantity,
                o.customer_id

                from
                products p
                join orders o
                on p.product_id=o.product_id
                group by
                o.product_id,
                o.customer_id
                ) c
        on c.product_id=b.product_id
        join customers d
        on c.customer_id=d.customer_id
        group by
        a.product_id,
        a.unit_price,
        c.max_monthly_quantity,
       customer_age_group,
       d.customer_age
        ) e
where rk=1
order by
        total_sales desc,
        product_id asc


发表于 2025-04-29 17:07:31 回复(1)
Mysql
SG9头像 SG9 
with sales as(select 
        product_id,
        unit_price,
        sum(quantity) as total_quantity,
        round(sum(quantity * unit_price), 2) as total_sales,
        round(sum(quantity * unit_price) / 12, 2) as avg_monthly_sales
    from orders o
    join products p using (product_id)
    group by product_id
),
monthly_quantity as
( 	select product_id,
	max(monthly_quantity) as max_monthly_quantity
    from
	(select 
		product_id,
		sum(quantity) as monthly_quantity
	from orders o
    join products p using (product_id)
    group by month(order_date), product_id) as a
    group by product_id
),
customers_max as(
	select
		customer_id,
        product_id,
	case when customer_age between '1' and '10' then '1-10'
     when customer_age between '11' and '20' then '11-20'
     when customer_age between '21' and '30' then '21-30'
     when customer_age between '31' and '40' then '31-40'
     when customer_age between '41' and '50' then '41-50'
     when customer_age between '51' and '60' then '51-60'
     else '61+ ' end as customer_age_group,
        quantity_customer as product_customers_max
	from(
		select customer_id,
		product_id,
        customer_age,
		sum(quantity) as quantity_customer,
        row_number() over (partition by product_id order by sum(quantity) desc) as rk
		from orders o
		join  customers c using (customer_id)
		group by product_id, o.customer_id
		order by product_id, customer_age asc) as b
	where rk = 1
)
select
    product_id,
    total_sales,
    unit_price,
    total_quantity,
    avg_monthly_sales,
    max_monthly_quantity,
    customer_age_group
from sales join monthly_quantity using(product_id)
join customers_max using (product_id)
order by total_sales desc, product_id asc
写了一整个小时，好崩溃
发表于 2025-04-16 19:00:56 回复(0)
Mysql
小小老牛头像 小小老牛
学习数仓的小伙伴可以看我的代码，根据数仓分层构建
dwd层清洗数据统一字段，dws按产品id，月份，消费者组初步聚合，ads层进一步聚合，最后筛选一下 
with dwd as (select distinct
    a.product_id,
    unit_price,
    quantity,
    left(order_date,7) date,
    case when customer_age between 1 and 10 then "01-10"
         when customer_age between 11 and 20 then "11-20"
         when customer_age between 21 and 30 then "21-30"
         when customer_age between 31 and 40 then "31-40"
         when customer_age between 41 and 50 then "41-50"
         when customer_age between 51 and 60 then "51-60"
         when customer_age >60 then "60+"   
         end age_group          
from products a 
inner join orders b on a.product_id=b.product_id inner join customers c on b.customer_id=c.customer_id),

dws as (select distinct
    *,
    sum(quantity) over(partition by product_id,date) month_num,
    sum(quantity*unit_price) over(partition by product_id,date) month_total,
    sum(quantity) over(partition by product_id,age_group) age_num
from dwd),

ads as(
select distinct
    product_id,
    sum(month_total) over(partition by product_id) total_sales,
    unit_price,
    sum(month_num) over(partition by product_id) total_quantity,
    round(sum(month_total) over(partition by product_id)/12,2) avg_monthly_sales,
    max(month_num) over(partition by product_id) max_monthly_quantity,
    age_group,
    row_number() over(partition by product_id order by age_num desc,age_group) flag
from dws
order by 2 desc ,1)
select
    product_id,
    total_sales,
    unit_price,
    total_quantity,
    avg_monthly_sales,
    max_monthly_quantity,
    age_group customer_age_group
from ads where flag=1


发表于 2025-04-08 19:14:20 回复(0)
提交观点

问题信息

SQL
来自：2024年淘天集团春招...
难度：
8条回答 414浏览

热门推荐

相关试题

统计每个产品的销售情况

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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