首页 > 试题广场 >

统计每个产品的销售情况

[编程题]统计每个产品的销售情况
  • 热度指数：11295 时间限制：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
算法知识视频讲解
添加回答
yummy爱赚钱头像 yummy爱赚钱 
with t1 as #计算 前5个字段
    (select 
        p.product_id
        ,sum(quantity*unit_price) total_sales
        ,unit_price
        ,sum(quantity) total_quantity
        ,round(sum(quantity*unit_price)/12,2) avg_monthly_sales
    from products p join orders o on p.product_id=o.product_id
    group by p.product_id,unit_price)
,t2 as #计算“产品的 最大 月销售数量”
    (select 
        product_id
        ,month(order_date) month
        ,sum(quantity) monthly_quantity
    from orders
    group by product_id,month)
,t3 as #计算“产品的 最大 月销售数量”
    (select 
        product_id
        ,max(monthly_quantity) max_monthly_quantity 
    from t2
    group by product_id)
,t4 as #计算顾客"年龄段"、“产品购买数”
    (select 
        product_id
        ,o.customer_id
        ,sum(quantity)  某顾客购买该产品的数量
        ,row_number()over(partition by product_id order by sum(quantity) desc,customer_age) c_quantity_rk
        ,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 customer_age_group 
    from customers c join orders o on c.customer_id=o.customer_id
    group by o.customer_id,product_id,customer_age_group)
,t5 as #计算 购买该产品的数量最多的顾客的年龄段
    (select 
        product_id,customer_age_group from t4
    where c_quantity_rk=1)
select 
    t1.product_id
    ,total_sales
    ,unit_price
    ,total_quantity
    ,avg_monthly_sales
    ,max_monthly_quantity
    ,customer_age_group
from t1
    join t3 on t1.product_id=t3.product_id
    join t5 on t1.product_id=t5.product_id
order by total_sales desc,product_id

发表于 2025-03-23 16:35:05 回复(1)
东方宸星头像 东方宸星
什么破题，又臭又长 
WITH initial_data AS (
    SELECT
        product_id,
        SUM(quantity * unit_price) AS total_sales,
        unit_price,
        SUM(quantity) AS total_quantity,
        ROUND(SUM(quantity * unit_price) / 12, 2) AS avg_monthly_sales
    FROM 
        orders
        LEFT JOIN products USING (product_id)
        LEFT JOIN customers USING (customer_id)
    WHERE 
        YEAR(order_date) = 2023
    GROUP BY
        product_id
),
monthly_data AS (
    SELECT 
        product_id,
        MONTH(order_date),
        SUM(quantity) AS monthly_quantity,
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY SUM(quantity) DESC) AS rk_monthly
    FROM 
        orders
        LEFT JOIN products USING (product_id)
        LEFT JOIN customers USING (customer_id)
    WHERE 
        YEAR(order_date) = 2023
    GROUP BY
        product_id,
        MONTH(order_date)
),
age_group_data AS (
    SELECT 
        product_id,
        CASE 
            WHEN customer_age >= 1 AND customer_age <= 10 THEN '1-10'
            WHEN customer_age >= 11 AND customer_age <= 20 THEN '11-20'
            WHEN customer_age >= 21 AND customer_age <= 30 THEN '21-30'
            WHEN customer_age >= 31 AND customer_age <= 40 THEN '31-40'
            WHEN customer_age >= 41 AND customer_age <= 50 THEN '41-50'
            WHEN customer_age >= 51 AND customer_age <= 60 THEN '51-60'
            ELSE '61+'
        END AS age_group,
        CASE 
            WHEN customer_age >= 1 AND customer_age <= 10 THEN 5
            WHEN customer_age >= 11 AND customer_age <= 20 THEN 15
            WHEN customer_age >= 21 AND customer_age <= 30 THEN 25
            WHEN customer_age >= 31 AND customer_age <= 40 THEN 35
            WHEN customer_age >= 41 AND customer_age <= 50 THEN 45
            WHEN customer_age >= 51 AND customer_age <= 60 THEN 55
            ELSE 65
        END AS age_gap,
        quantity
    FROM 
        orders
        LEFT JOIN products USING (product_id)
        LEFT JOIN customers USING (customer_id)
    WHERE 
        YEAR(order_date) = 2023

),
age_quantity_data AS (
    SELECT
        product_id,
        age_group,
        SUM(quantity) AS age_group_quantity,
        RANK() OVER (PARTITION BY product_id ORDER BY SUM(quantity) DESC, age_gap) AS rk_age
    FROM 
        age_group_data
    GROUP BY 
        product_id,
        age_group,
        age_gap
)
SELECT
    product_id,
    total_sales,
    unit_price,
    total_quantity,
    avg_monthly_sales,
    monthly_quantity AS max_monthly_quantity,
    age_group AS customer_age_group
FROM 
    initial_data
    LEFT JOIN monthly_data USING (product_id)
    LEFT JOIN age_quantity_data USING (product_id)
WHERE 
    rk_monthly = 1
    AND rk_age = 1
ORDER BY 
    total_sales DESC,
    product_id


发表于 2025-07-01 12:33:19 回复(0)
专注当下喲头像 专注当下喲 
with t as (select product_id,min(customer_age) as kk from (select d.product_id,customer_age
from orders d
inner join customers e
inner join (select product_id,max(quantity) as max_q from orders 
group by product_id) as l
on d.customer_id=e.customer_id
and d.product_id=l.product_id
where quantity=max_q) aa
group by aa.product_id),
y as (select product_id,max(monthly_quantity) as max_monthly_quantity from (select product_id,sum(quantity) as monthly_quantity from orders 
group by product_id,DATE_FORMAT(order_date, '%Y-%m') ) bb
group by product_id)

select q.product_id,total_sales,unit_price,total_quantity,round(avg_monthly_sales,2) as avg_monthly_sales,y.max_monthly_quantity,(case when t.kk>=1 and t.kk<=10 then '1-10' when t.kk>=11 and t.kk<=20 then '11-20' when t.kk>=21 and t.kk<=30 then '21-30' when t.kk>=31 and t.kk<=40 then '31-40' when t.kk>=41 and t.kk<=50 then '41-50' when t.kk>=51 and t.kk<=60 then '51-60' else '61+' end) as customer_age_group
from(
select a.product_id,sum(quantity) *unit_price as total_sales,unit_price,
sum(quantity) as total_quantity,(sum(quantity) *unit_price)/12 as avg_monthly_sales
from orders a
inner join products b
inner join customers c
on a.customer_id=c.customer_id
and a.product_id=b.product_id
group by a.product_id,unit_price
order by total_sales desc,a.product_id) q
left join t on q.product_id=t.product_id
left join y on q.product_id=y.product_id

难吐了，4小时，你知道我这四小时是怎么过的吗？ 
啊啊啊啊啊啊啊！！！1



发表于 2025-03-19 17:53:48 回复(4)
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)
牛客164735395号头像 牛客164735395号 
with 
a as(select product_id , month(order_date) month, sum(quantity) quan,
row_number()over(partition by product_id order by sum(quantity) desc) ro
from orders group by product_id , month(order_date)),
b as (select o.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) ag , sum(quantity) cnt,
    row_number()over(partition by product_id order by sum(quantity) desc) ra
    from customers c
    join orders o on o.customer_id = c.customer_id 
    group by product_id,ag)
select o.product_id,
sum(p.unit_price*o.quantity) total_sales,
p.unit_price,
sum(o.quantity) total_quantity,
round(sum(p.unit_price*o.quantity)/12,2) avg_monthly_sales,
a.quan max_monthly_quantity,
b.ag customer_age_group
from orders o 
join products p on p.product_id = o.product_id
join a on a.product_id = o.product_id
join b on b.product_id = o.product_id
where a.ro = 1 and b.ra = 1
group by o.product_id,a.quan,b.ag
order by total_sales desc,product_id 
前4条数据简单,5和6分别通过a表和b表用窗口函数来进行排序,其实拆开并不难,就是东西多了杂到一起就晕了
发表于 2025-05-02 22:58:03 回复(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)
4fbd81e头像 4fbd81e
没什么说的，关键是耐心。
with t1 as(
    select p.*, o.order_id,o.customer_id,o.quantity,o.order_date,
    c.customer_name,  c.customer_email, c.customer_age,
    ELT(INTERVAL(c.customer_age,1,11,21,31,41,51,61),'1-10','11-20','21-30','31-40','41-50','51-60','61+') as age_group,
    month(o.order_date) as order_month
    from products p
    left join orders o
    on o.product_id=p.product_id
    left join customers c
    on c.customer_id=o.customer_id
    where year(o.order_date)=2023
)
,
result1 as(
    select product_id, max(total_quantity) as max_monthly_quantity
    from (
        select product_id,
        sum(quantity) as total_quantity
        from t1 
        group by product_id,order_month
    ) tmp1
    group by product_id
)
,
t3 as (
    select product_id,
    sum(quantity) as total_quantity,
    age_group
    from t1 
    group by product_id,age_group
)
,
result2 as(
    select product_id,age_group
    from  (
            select *, row_number() over(partition by product_id order by total_quantity desc, field(age_group,'1-10','11-20','21-30','31-40','41-50','51-60','61+') ) as rn from t3 
          ) tmp2
    where rn=1
)
,
result0 as(
    select 
    product_id,
    sum(quantity*unit_price) as total_sales,
    unit_price,
    sum(quantity) as total_quantity,
    round(sum(quantity*unit_price) /12.0, 2) as avg_monthly_sales
    from t1
    group by product_id
)

select r0.*,
r1.max_monthly_quantity,
r2.age_group as customer_age_group
from result0 r0
join result1 r1
on r0.product_id=r1.product_id
join result2 r2
on r0.product_id=r2.product_id
order by total_sales desc, product_id asc


发表于 2025-04-15 14:17:39 回复(0)
牛客806321860号头像 牛客806321860号
select t2.product_id, total_sales,unit_price, total_quantity,
avg_monthly_sales,max_monthly_quantity,customer_age_group
from(
select p.product_id,sum(quantity) *unit_price as total_sales,unit_price,sum(quantity) as total_quantity,round(sum(quantity) *unit_price/12,2) as avg_monthly_sales
from products p join orders o
on p.product_id=o.product_id
group by p.product_id,unit_price)t1
join
(select product_id,quantity 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 '61+ '
end as customer_age_group
from(
select product_id,quantity,customer_age,row_number() over(partition by product_id order by quantity desc,customer_age) as rk
from customers c join orders o
on c.customer_id=o.customer_id)t
where rk=1)t2
on t1.product_id=t2.product_id
order by total_sales desc,product_id; 
select t2.product_id, total_sales,unit_price, total_quantity,
avg_monthly_sales,max_monthly_quantity,customer_age_group
from(
select p.product_id,sum(quantity) *unit_price as total_sales,unit_price,sum(quantity) as total_quantity,round(sum(quantity) *unit_price/12,2) as avg_monthly_sales
from products p join orders o
on p.product_id=o.product_id
group by p.product_id,unit_price)t1
join
(select product_id,quantity 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 '61+ '
end as customer_age_group
from(
select product_id,quantity,customer_age,row_number() over(partition by product_id order by quantity desc,customer_age) as rk
from customers c join orders o
on c.customer_id=o.customer_id)t
where rk=1)t2
on t1.product_id=t2.product_id
order by total_sales desc,product_id;

发表于 2025-04-13 16:10:01 回复(1)
牛客695261949号头像 牛客695261949号
购买量最多的客户年龄段（customer_age_group）
1、每个产品每个用户购买数量
2、根据每个产品每个用户购买数量和用户年龄，统计购买数量降序年龄升序排名
3、排名第一的用户年龄
4、处理年龄区间

select product_id , total_sales , unit_price, 
         total_quantity,
        round(avg_monthly_sales,2) as avg_monthly_sales,
         max_monthly_quantity,
        case when maxq_age>=1 and maxq_age<=10 then '1-10'
        when maxq_age>=11 and maxq_age<=20 then'11-20'
        when maxq_age>=21 and maxq_age<=30 then'21-30'
        when maxq_age>=31 and maxq_age<=40 then'31-40'
        when maxq_age>=41 and maxq_age<=50 then'41-50'
        when maxq_age>=51 and maxq_age<=60 then'51-60'
        when maxq_age>=61 then'61+'
        end as customer_age_group
from (
    select product_id ,sum(sales) as total_sales , max(unit_price ) as unit_price, 
        sum(quantity) aS total_quantity,
        sum(sales)/12 aS avg_monthly_sales,
        max(mon_quantity) as max_monthly_quantity,
        排名第一的用户年龄
        max(case when rn =1 then customer_age end ) as maxq_age
    from (

        select customer_id , product_id ,quantity, sales, order_date,unit_price,customer_age,
            cus_quantity,
            mon_quantity,
            根据每个产品每个用户购买数量和用户年龄，统计购买数量降序年龄升序排名
            row_number()over(partition by product_id order by cus_quantity desc,customer_age) as rn
        from (
            select o.customer_id , o.product_id ,quantity, quantity*p.unit_price as sales, order_date,unit_price,c.customer_age,
            每个产品每个用户购买数量
                sum(quantity)over(partition by o.product_id, o.customer_id) as cus_quantity,
                月购买数量
                sum(quantity)over(partition by o.product_id, substr(order_date,1,7)) as mon_quantity
            from orders o inner join products p on o.product_id= p.product_id
            inner join customers c on o.customer_id= c.customer_id
            where substr(order_date,1,4)='2023'
        ) t 
    ) t
     group by product_id
)t

order by 2 desc ,1




发表于 2025-04-07 13:40:06 回复(1)
讲道理的熊熊整顿职场头像 讲道理的熊熊整顿职场 
#关联products和orders求得前六项
with a as(
    select p.product_id,
        round(sum(quantity*unit_price),2) total_sales,
        unit_price,
        sum(quantity) total_quantity,
        round((sum(quantity*unit_price))/12,2) avg_monthly_sales,
        max(quantity) max_monthly_quantity
    from products p 
    join orders o using(product_id)
    where year(order_date)=2023 
    group by p.product_id
),
#关联customers和orders分类，并用窗口函数根据product_id分组customer_age升序排序rk
 b as(
    select *,
    rank() over(partition by product_id order by customer_age) rk
    from 
    (select c.customer_id,product_id,customer_age,
    max(sum(quantity)) over(partition by product_id ) num,
    sum(quantity) um,
        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 customer_age_group
    from customers c left join orders o using(customer_id)
    group by c.customer_id,product_id)d
    where num=um 
)


	#关联a、b表，并得购买量都是最高时最小年龄段 


select a.product_id,
    total_sales,
    unit_price,
    total_quantity,
    avg_monthly_sales,
    max_monthly_quantity,
    customer_age_group
from a join b using(product_id)
where rk=1
order by total_sales desc,product_id

发表于 2025-03-26 17:57:49 回复(1)
牛客463374079号头像 牛客463374079号
with tmp as
(
select product_id, sum(quantity) as total_quantity, max(quantity) as max_monthly_quantity
from orders
group by product_id
),
t1 as (
select tmp.product_id as product_id, tmp.total_quantity*p.unit_price as total_sales, p.unit_price as unit_price, tmp.total_quantity as total_quantity, round(tmp.total_quantity*p.unit_price/12,2) as avg_monthly_sales, tmp.max_monthly_quantity as max_monthly_quantity
from tmp join products as p on tmp.product_id=p.product_id
order by 2 desc
),
t2 as (
select tmp.product_id as product_id, o.customer_id as customer_id, o.quantity as quantity
from tmp join orders o on tmp.product_id=o.product_id
where tmp.max_monthly_quantity=o.quantity
),
t3 as (
select t2.product_id as product_id, if(c.customer_age%10>0,concat(floor(c.customer_age/10)*10+1,'-',ceil(c.customer_age/10)*10),concat((floor(c.customer_age/10)-1)*10+1,'-',floor(c.customer_age/10)*10)) as customer_age_group
from t2 join customers as c on t2.customer_id=c.customer_id
),
t4 as (
select *, rank()over(partition by product_id order by customer_age_group) as rk
from t3
),
t5 as (
select *
from t4
where rk=1
)
select t1.product_id as product_id, t1.total_sales as total_sales, t1.unit_price as unit_price, t1.total_quantity as total_quantity, t1.avg_monthly_sales as avg_monthly_sales, t1.max_monthly_quantity as max_monthly_quantity, t5.customer_age_group as customer_age_group
from t1 join t5 on t1.product_id=t5.product_id
order by total_sales desc
发表于 2025-07-09 01:30:43 回复(0)
牛客704457720号头像 牛客704457720号
with tmp as(
    select distinct
        o.product_id,sum(unit_price*quantity)over(partition by o.product_id) total_sales,unit_price,
        sum(quantity)over(partition by o.product_id) total_quantity,
        round(sum(unit_price*quantity/12)over(partition by o.product_id),2) avg_monthly_sales,
        quantity,row_number()over(partition by o.product_id order by quantity desc,customer_age)rn,
        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 customer_age_group
from orders o
join products p on o.product_id = p.product_id
join customers c on o.customer_id = c.customer_id
)select
    product_id,total_sales,unit_price,total_quantity,avg_monthly_sales,
    quantity max_monthly_quantity,customer_age_group
from tmp where rn = 1
order by total_sales desc,product_id;
发表于 2025-07-08 10:35:16 回复(0)
乖乖大狗子头像 乖乖大狗子
WITH
    order_hanled_tb AS (
        SELECT
            o.product_id AS product_id,
            quantity,
            month(order_date) AS order_month,
            p.unit_price AS unit_price,
            customer_id
        FROM
            orders o
            JOIN products p ON o.product_id = p.product_id
    ),
    total_tb AS (
        SELECT
            product_id,
            ROUND(SUM(quantity * unit_price), 2) AS total_sales,
            ROUND(AVG(unit_price), 2) AS unit_price,
            SUM(quantity) AS total_quantity
        FROM
            order_hanled_tb
        GROUP BY
            product_id
    ),
    month_order_tb AS (
        SELECT
            product_id,
            MAX(month_quantity) AS max_monthly_quantity
        FROM
            (
                (
                    SELECT
                        product_id,
                        SUM(quantity) AS month_quantity,
                        order_month
                    FROM
                        order_hanled_tb
                    GROUP BY
                        product_id,
                        order_month
                )
            ) sub
        GROUP BY
            product_id
    ),
    sum_customer_tb AS (
        SELECT
            product_id,
            customer_id,
            SUM(quantity) AS ct_quantity
        FROM
            orders
        GROUP BY
            product_id,
            customer_id
    ),
    max_customer_tb AS (
        SELECT
            product_id,
            sub.customer_id AS customer_id,
            c.customer_age AS age,
            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
            (
                SELECT
                    product_id,
                    customer_id,
                    ROW_NUMBER() OVER (
                        PARTITION BY
                            product_id
                        ORDER BY
                            ct_quantity DESC
                    ) AS rank1
                FROM
                    sum_customer_tb sct
            ) sub
            JOIN customers c ON sub.customer_id = c.customer_id
        WHERE
            rank1 = 1
    )
SELECT
    tt.product_id AS product_id, total_sales,unit_price,total_quantity, ROUND(total_sales / 12, 2) AS avg_monthly_sales, mot.max_monthly_quantity AS max_monthly_quantity, mct.customer_age_group AS customer_age_group
FROM
    total_tb tt
JOIN month_order_tb mot ON tt.product_id = mot.product_id
JOIN max_customer_tb mct ON tt.product_id = mct.product_id
ORDER BY total_sales DESC

难泵

发表于 2025-07-07 14:55:00 回复(0)
牛客956700257号头像 牛客956700257号
with used_a as(select order_id, orders.product_id, unit_price, orders.customer_id, customer_age, quantity, order_date, unit_price*quantity total_perorder
from orders
left join products on orders.product_id = products.product_id
left join customers on orders.customer_id =  customers.customer_id
)
,
former5 as (select product_id, sum(total_perorder) total_sales, unit_price, sum(quantity) total_quantity, round(sum(total_perorder)/12,2) avg_monthly_sales
from used_a
group by product_id,unit_price
)
,
maxmonth_tb as (select product_id, max(quantity_perm) max_monthly_quantity
from(
select product_id, month(order_date) m, sum(quantity) quantity_perm
from used_a
group by product_id, month(order_date)
) b
group by product_id
)
,
used_c as(select product_id, quantity, customer_age, (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 used_a
)
,
agegroup as (select product_id, customer_age_group
from(
select product_id, customer_age_group, rank()over(partition by product_id order by q desc, customer_age_group) rk_age
from(select product_id,customer_age_group,sum(quantity) q
from used_c
group by product_id,customer_age_group
) d
)e
where rk_age =1
)

select former5.product_id, total_sales, unit_price, total_quantity, avg_monthly_sales,max_monthly_quantity, customer_age_group
from former5
inner join maxmonth_tb on former5.product_id = maxmonth_tb.product_id
inner join agegroup on former5.product_id= agegroup.product_id
order by total_sales desc, former5.product_id
发表于 2025-07-07 11:48:05 回复(0)
牛客677654913号头像 牛客677654913号 
with t1 as( -- 前6个字段
select 
    o.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,
    max(quantity) as max_monthly_quantity
from orders o
join products p on o.product_id = p.product_id
where year(order_date) = '2023'
group by 1,3),

t3 as( -- 销售量排序（为了选出购买该产品数量最多的顾客）
    select
        product_id,
        customer_id,
        rank() over(partition by product_id order by tq desc,customer_age) as rk,
        age_group
    from( -- 每个产品对应的每个顾客的销售量
        select
            product_id,
            o.customer_id,
            sum(quantity) as tq,
            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'
            else '61+' end as age_group
        from orders o
        join  customers c on o.customer_id = c.customer_id
        where year(order_date) = '2023'
        group by 1,2,4,5) t2)

select -- 加最后一个字段：购买量最多的客户年龄段
    t1.product_id,
    t1.total_sales,
    t1.unit_price,
    t1.total_quantity,
    t1.avg_monthly_sales,
    t1.max_monthly_quantity,
    t3.age_group as customer_age_group
from t1
join t3 on t1.product_id = t3.product_id and t3.rk = 1
order by t1.total_sales desc, t1.product_id

发表于 2025-07-02 11:45:37 回复(0)
牛客710494782号头像 牛客710494782号
# 查询 total_quantity
with total_count as(
    select product_id, sum(quantity) total_quantity
    from orders
    group by product_id
),

# 查询月最大销量
month_max_count as (
    select product_id, max(month_count) month_cnt
    from (
        select product_id, date_format(order_date, '%M'), sum(quantity) month_count
        from orders
        group by product_id, date_format(order_date, '%M')
    ) as month
    group by product_id
),
# 查询年龄段
age_layer as (
    select customer_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 >= 61 then '60+'
        end age
    from customers
),

# 查询产品中购买量最大的用户，如果两个用户一样，则展示年龄最小的那个
# 每个产品每个用户购买数量
product_user_count as(
    select product_id,
        customer_id,
        sum(quantity) user_total
    from orders
    group by product_id, customer_id
),
#每个产品最大购买量的用户
product_buy_max_user as(
    select puc.product_id,
        puc.customer_id,
        puc.user_total,
        c.customer_age,
        dense_rank()over(partition by puc.product_id order by puc.user_total desc) rk
    from product_user_count puc
        join customers c on puc.customer_id = c.customer_id
),

count_age_rank as (
    select product_id,
       customer_id,
       user_total,
       customer_age
    from (
        select product_id,
        customer_id,
        user_total,
        customer_age,
        row_number()over(partition by product_id order by customer_age asc) inner_rk
        from product_buy_max_user
        where rk = 1
    ) final
    where inner_rk = 1
),
final_age as(
    select car.product_id, car.customer_id, car.customer_age, al.age
    from count_age_rank car
        join age_layer al on al.customer_id = car.customer_id
)

select p.product_id,
    round(tc.total_quantity * p.unit_price, 2) total_sales,
    p.unit_price,
    tc.total_quantity,
    round(tc.total_quantity * p.unit_price / 12, 2) avg_monthly_sales,
    mmc.month_cnt max_monthly_quantity,
    fa.age customer_age_group
from products p
    join total_count tc on p.product_id = tc.product_id
    join month_max_count mmc on p.product_id = mmc.product_id
    join final_age fa on p.product_id = fa.product_id
order by total_sales desc,
    p.product_id asc
发表于 2025-07-01 10:27:15 回复(0)
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)
牛客314411609号头像 牛客314411609号
with t1 as
(
    select
    product_id,customer_id,customer_age
    from
    (
        select
        t.product_id,t.customer_id,c.customer_age,
        row_number() over(partition by t.product_id order by t.qua desc,c.customer_age) as rn
        from
        (
            select
            product_id,customer_id,
            sum(quantity) as qua
            from orders
            where year(order_date)=2023
            group by product_id,customer_id
        ) t
        join customers c on t.customer_id=c.customer_id
    ) r
    where rn=1
),
t2 as
(
    select
    product_id,
    max(s) as ms
    from
    (
        select
        product_id,month(order_date) as m,
        sum(quantity) as s
        from orders
        where year(order_date)=2023
        group by product_id,month(order_date)
    ) t
    group by product_id
),
t3 as
(
    select
    o.product_id,
    sum(o.quantity)*avg(p.unit_price) as total_sales,
    avg(p.unit_price) as unit_price,
    sum(o.quantity) as total_quantity,
    sum(o.quantity)*avg(p.unit_price)/12 as avg_monthly_sales
    from orders o
    join products p on o.product_id=p.product_id
    where year(o.order_date)=2023
    group by o.product_id
)

select
t3.product_id,
round(t3.total_sales,2) as total_sales,
round(t3.unit_price,2) as unit_price,
t3.total_quantity,
round(t3.avg_monthly_sales,2) as avg_monthly_sales,
t2.ms as max_monthly_quantity,
case when t1.customer_age between 1 and 10 then '1-10'
     when t1.customer_age between 11 and 20 then '11-20'
     when t1.customer_age between 21 and 30 then '21-30'
     when t1.customer_age between 31 and 40 then '31-40'
     when t1.customer_age between 41 and 50 then '41-50'
     when t1.customer_age between 51 and 60 then '51-60'
     when t1.customer_age > 60 then '61+'
end as customer_age_group
from t1
join t2 on t1.product_id=t2.product_id
join t3 on t2.product_id=t3.product_id
order by t3.total_sales desc,t3.product_id
发表于 2025-06-22 18:54:15 回复(0)
GN0000Raiser头像 GN0000Raiser
with t1 as (select *
from orders o
left join products using(product_id)
left join customers using(customer_id)),
t2 as (select product_id,
sum(unit_price*quantity) total_sales,
unit_price, sum(quantity) total_quantity,
round(sum(unit_price*quantity)/12,2) avg_monthly_sales,
max(quantity) max_monthly_quantity
from t1
group by product_id,unit_price
),
t3 as (select t2.product_id product_id,
total_sales,
t2.unit_price unit_price,
total_quantity,
avg_monthly_sales,
max_monthly_quantity,
t1.customer_age 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'
else '60+'
end) as customer_age_group
from t2 left join t1 on t2.product_id= t1.product_id and t2.max_monthly_quantity=t1.quantity
order by total_sales desc, product_id),
t4 as (select product_id, customer_age,rank()over (partition by product_id order by customer_age) rk
from t3)
select t4.product_id product_id,
total_sales,
unit_price,
total_quantity,
avg_monthly_sales,
max_monthly_quantity,
customer_age_group
from t4 left join t3 on t4.product_id= t3.product_id and t4.customer_age = t3.customer_age
where rk=1
order by total_sales desc, product_id ASC
发表于 2025-06-20 22:12:22 回复(0)
牛客928265197号头像 牛客928265197号

with t0 as

(

select a.product_id

        ,unit_price

        ,quantity

        ,case when customer_age 11 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 age_group

        ,order_date

from orders as a

join customers as b on a.customer_id=b.customer_id

join products as c on a.product_id=c.product_id

where year(order_date)='2023'

)

,t1 as

( # 按产品和月份计算销售数据

select product_id

        ,month(order_date) as month

        ,sum(quantity*unit_price) as month_total_price

        ,sum(quantity) as month_quantity

from t0

group by 1,2

)

,t2 as

( #每个产品购买数量最多的顾客的年龄段(order by当存在两个客户年购买量都是最高时)

select product_id

        ,age_group as customer_age_group

from

(

    select product_id

            ,age_group

            ,row_number() over(partition by product_id order by total_quantity desc,product_id) as rn

    from

    (

        select product_id

                ,age_group

                ,sum(quantity) as total_quantity

        from t0

        group by 1,2

    ) tmp

) p

where rn=1

)

,t3 as

(

select product_id

        ,sum(month_total_price) as total_sales

        ,sum(month_quantity) as total_quantity

        ,round(sum(month_total_price)/12,2) as avg_monthly_sales

        ,max(month_quantity) as max_monthly_quantity

from t1

group by 1

)

select distinct a.product_id

    ,total_sales

    ,unit_price

    ,total_quantity

    ,avg_monthly_sales

    ,max_monthly_quantity

    ,customer_age_group

from t3 as a

inner join t2 as b on a.product_id=b.product_id

inner join t0 as c on a.product_id=c.product_id and b.product_id=c.product_id

order by 2 desc,1

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

问题信息

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

热门推荐

相关试题

统计每个产品的销售情况

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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