首页 > 试题广场 >

统计每个产品的销售情况

[编程题]统计每个产品的销售情况
  • 热度指数：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
算法知识视频讲解
添加回答
Sqlite
汪星鱼头像 汪星鱼
select
a.product_id,
total_sales,
unit_price,
total_quantity,
round(avg_monthly_sales, 2),
max_monthly_quantity,
customer_age_group
from
(
select
p.product_id,
unit_price,
sum(quantity) total_quantity,
sum(quantity) * unit_price total_sales,
sum(quantity) * unit_price / 12 avg_monthly_sales
from
products p
join orders o on p.product_id = o.product_id
where
year (order_date) = 2023
group by
p.product_id,
unit_price
) a 
join (
select
product_id,
concat ('-', year (order_date), month (order_date)) yf,
sum(quantity) max_monthly_quantity,
row_number() over (
partition by
product_id
order by
sum(quantity) desc
) rk
from
orders
where
year (order_date) = 2023
group by
product_id,
concat ('-', year (order_date), month (order_date))
) b on a.product_id = b.product_id
join (
select
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'
when customer_age > 60 then '60+'
end as customer_age_group,
row_number() over (
partition by
product_id
order by
sum(quantity) desc,
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'
when customer_age > 60 then '60+'
end
) rk1
from
orders o
join customers c on o.customer_id = c.customer_id
where
year (order_date) = 2023
group by
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'
when customer_age > 60 then '60+'
end
) c on a.product_id = c.product_id
where
rk = 1
and rk1 = 1
order by
total_sales desc,
product_id


发表于 2025-06-11 11:35:00 回复(0)
Sqlite
牛客407121320号头像 牛客407121320号
with t1 as (
select
    p.product_id,
    round(p.unit_price * sum(o.quantity),2) as total_sales,
    p.unit_price,
    sum(o.quantity) total_quantity,
    round(p.unit_price * sum(o.quantity)/12,2) avg_monthly_sales
from orders o
join products p on p.product_id = o.product_id
group by p.product_id
),
t2 as(
    select
        product_id,
        month(order_date)order_month,
        sum(quantity) as month_quantity
    from orders
    group by product_id,order_date
),
t3 as(
    select
        product_id,
        max(month_quantity)max_monthly_quantity
    from t2
    group by product_id
),
t4 as (
select
    product_id,
    customer_age_group
from
    (select
        o.product_id,
        o.customer_id,
        sum(o.quantity),
        row_number() over (partition by o.product_id order by sum(o.quantity) desc,customer_age) rn,
        case when c.customer_age between 1 and 10 then '1-10'
            when c.customer_age between 11 and 20 then '11-20'
            when c.customer_age between 21 and 30 then '21-30'
            when c.customer_age between 31 and 40 then '31-40'
            when c.customer_age between 41 and 50 then '41-50'
            when c.customer_age between 51 and 60 then '51-60'
            else '61+' end as customer_age_group
    from orders o  
    join customers c on o.customer_id = c.customer_id
    group by o.product_id,customer_age_group,o.customer_id)a
where rn =1
)
select
    t1.product_id,
    t1.total_sales,
    t1.unit_price,
    t1.total_quantity,
    t1.avg_monthly_sales,
    t3.max_monthly_quantity,
    t4.customer_age_group
from t1
join t3 on t1.product_id = t3.product_id
join t4 on t1.product_id = t4.product_id
order by t1.total_sales desc,t1.product_id
发表于 2025-05-16 15:34:35 回复(0)
Sqlite
毛线球与猫头像 毛线球与猫 
with a as(
    select customer_id, customer_age,
    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' end as customer_age_group
    from
    customers
),

b as (
    select *, quantity * unit_price as sales
    from products
    left join orders using(product_id)
    left join a using(customer_id)
),

c as (select 
product_id, 
sum(sales) as total_sales,
unit_price, 
sum(quantity) as total_quantity,
round(sum(sales)/12,2) as avg_monthly_sales
from b
group by product_id, unit_price),

d as (select product_id, max(monthly_quantity) as max_monthly_quantity 
from
 (select product_id, sum(quantity) as monthly_quantity
from b
group by product_id, month(order_date)) as t1
group by product_id),

e as (select * from (select product_id, customer_id, customer_age_group, sum(quantity) as sum_quantity, 
row_number()over(partition by product_id order by sum(quantity) desc, customer_age) as rk
from b 
group by product_id, customer_id, customer_age_group) as t2
where rk = 1)

select product_id, total_sales, unit_price, total_quantity, avg_monthly_sales, max_monthly_quantity, customer_age_group
from c
left join d using(product_id)
left join e using(product_id)
order by total_sales desc, product_id

发表于 2025-05-01 00:04:30 回复(0)
Sqlite
真知棒仔仔棒头像 真知棒仔仔棒 
with t1 as(
	select	distinct o.product_id,
			sum(quantity * unit_price) over(partition by o.product_id) total_sales,
			unit_price,
			sum(quantity) over(partition by o.product_id) total_quantity,
			round(sum(quantity * unit_price) over(partition by o.product_id) /12,2) avg_monthly_sales,
			max(quantity) over(partition by o.product_id) max_monthly_quantity
	from	orders o
	join	customers c on o.customer_id = c.customer_id
	join	products p on o.product_id = p.product_id
),
t2 as(
		select	product_id,
				case	
					when customer_age > 0 and customer_age <= 10 then '1-10'
					when customer_age > 10 and customer_age <= 20 then '11-20'
					when customer_age > 20 and customer_age <= 30 then '21-30'
					when customer_age > 30 and customer_age <= 40 then '31-40'
					when customer_age > 40 and customer_age <= 50 then '41-50'
					when customer_age > 50 and customer_age <= 60 then '51-60'
					when customer_age > 60 then '61+'
					end as customer_age_group,
					sum(quantity) cnt
		from	orders o
		join	customers c on o.customer_id = c.customer_id
		group by product_id,customer_age_group
),
t3 as(
		select	product_id,
				customer_age_group,
				row_number() over(partition by product_id order by cnt desc,customer_age_group asc) rk
		from t2
		order by rk
)
select	t1.*,
		customer_age_group
from t1
join t3 on t1.product_id = t3.product_id
where rk = 1
order by total_sales desc

难在最后一列，一个指标用了两个子查询

发表于 2025-04-27 14:29:01 回复(0)
Sqlite
牛客791227328号头像 牛客791227328号

-- 子查询 t1：计算每个产品的最大月销量和总销量

with t1 as (
    select
        product_id,
        max(m_qu) as max_monthly_quantity,
        sum(m_qu) as total_quantity
    from (
        select
            product_id,
            month(order_date),
            sum(quantity) over(partition by product_id, month(order_date)) as m_qu
        from orders
        where
            order_date between '2023-01-01' and '2023-12-31'
    ) a
    group by product_id
),
-- 子查询 t2：计算每个产品的总销售额、单价、总销量和月平均销售额
t2 as (
    select
        t1.product_id,
        round(p.unit_price * t1.total_quantity, 2) as total_sales,
        p.unit_price,
        t1.total_quantity,
        round(p.unit_price * t1.total_quantity / 12, 2) as avg_monthly_sales,
        t1.max_monthly_quantity
    from t1
    join products p on t1.product_id = p.product_id
),
-- 子查询 t3：计算每个产品每个客户的购买量和客户年龄，筛选 2023 年的订单
t3 as (
    select
        a.product_id,
        sum(a.quantity) over(partition by a.product_id, a.customer_id) as cq,
        b.customer_age
    from orders a
    join customers b on a.customer_id = b.customer_id
    where a.order_date between '2023-01-01' and '2023-12-31'
),
-- 子查询 t4：对每个产品的客户购买量进行排名
t4 as (
    select
        t2.product_id,
        t2.total_sales,
        t2.unit_price,
        t2.total_quantity,
        t2.avg_monthly_sales,
        t2.max_monthly_quantity,
        t3.cq,
        t3.customer_age,
        row_number() over(partition by t2.product_id order by t3.cq desc, t3.customer_age asc) as r
    from t2
    join t3 on t2.product_id = t3.product_id
)
-- 主查询：选择所需信息并进行年龄段转换，筛选排名为 1 的记录，按要求排序
select
    product_id,
    total_sales,
    unit_price,
    total_quantity,
    avg_monthly_sales,
    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'
        when customer_age >= 61 then '61+'
        else null
    end as customer_age_group
from t4
where r = 1
order by
    total_sales desc,
    product_id asc;

写了一下午，一直报错，头大，改了好多次
发表于 2025-04-26 18:58:29 回复(1)
Sqlite
达尔文先生_头像 达尔文先生_
with tmp as
(   # 查询所有非聚合字段的基础数据
    select
    t1.product_id product_id
    ,t1.customer_id
    ,unit_price
    ,quantity
    ,order_date
    ,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
    from orders t1
    join products t2 on t1.product_id = t2.product_id
    join customers t3 on t1.customer_id = t3.customer_id
),tmp2 as
(
    # 查询总销售额，总销售数量，月均销售数量
    select t1.product_id product_id
    ,sum(unit_price*quantity) total_sales
    ,sum(quantity) total_quantity
    ,round(sum(unit_price*quantity) / 12,2) avg_monthly_sales
    from orders t1
    join products t2 on t1.product_id = t2.product_id
    group by 1
),tmp3 as
(
    # 查询最大月销售数量
    select product_id
    ,max(month_quantity) max_monthly_quantity
    from
    (
        select product_id
        ,sum(quantity) month_quantity
        from orders
        group by 1,month(order_date)
    ) a
    group by 1
),tmp4 as
(
    # 查询每类商品买的最多的客户信息
    select
    product_id
    ,customer_id
    from (
        select product_id
        ,customer_id
        ,row_number() over (partition by product_id order by cnt desc,customer_age) rk
        from
        (
            select product_id
            ,t1.customer_id customer_id
            ,customer_age
            ,sum(quantity) cnt
            from orders t1
            join customers t2 using(customer_id)
            group by 1,2,3
        ) a
    ) b
    where rk = 1
)

# 开始进行表连接即可
select
tmp4.product_id
,total_sales
,unit_price
,total_quantity
,avg_monthly_sales
,max_monthly_quantity
,customer_age_group
from tmp
join tmp2 on tmp.product_id = tmp2.product_id
join tmp3 on tmp.product_id = tmp3.product_id
right join tmp4 on tmp.product_id = tmp4.product_id and
tmp.customer_id = tmp4.customer_id
order by 2 desc,1

发表于 2025-04-25 17:00:19 回复(0)
Sqlite
我真的也叫乐杨头像 我真的也叫乐杨 
#计算商品总销售额，单价，总数，月均销售额
with t1 as
(select o.product_id as product_id, unit_price*sum(quantity) as total_sales, unit_price, sum(quantity) as total_quantity, round(unit_price*sum(quantity)/12,2) as avg_monthly_sales from orders o left join products p on o.product_id = p.product_id
group by product_id),
#计算最大月销售量
t2 as 
(select product_id, month, month_quantity as max_monthly_quantity from
(select product_id, month(order_date) as month, sum(quantity) as month_quantity, row_number()over(partition by product_id order by sum(quantity) desc) as rk from orders
group by product_id, month(order_date)) s1
where rk = 1),
#处理年龄
t3 as
(select product_id, customer_age, 
(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'
when customer_age >= 61 then '61+'
end) customer_age_group
from
(select product_id,o.customer_id as customer_id, sum(quantity) as sq, customer_age, row_number()over(partition by product_id order by sum(quantity) desc, customer_age) as rk from orders o left join customers c on o.customer_id = c.customer_id
group by product_id, o.customer_id) s2
where rk = 1)
#连接表
select t1.product_id as product_id, total_sales, unit_price, total_quantity, avg_monthly_sales, max_monthly_quantity, customer_age_group from t1 join t2 on t1.product_id = t2.product_id
join t3 on t1.product_id = t3.product_id
order by total_sales desc, product_id

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

问题信息

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

热门推荐

相关试题

统计每个产品的销售情况

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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