首页 > 试题广场 >

精品咖啡连锁门店王牌产品及其最忠实顾客分析

[编程题]精品咖啡连锁门店王牌产品及其最忠实顾客分析
  • 热度指数：757 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解

一、题目描述

【背景】
某精品咖啡连锁品牌在多个城市开设门店，所有门店共享统一的订单系统。运营团队需要为每家门店找出其"王牌产品"（销售额最高的产品），以及该王牌产品的"最忠实顾客"（购买该产品数量最多的顾客）。该分析需要两步关联：先定位每家门店的王牌产品，再基于该产品进一步找出该产品的头号顾客。

【表结构与字段说明】

表1：coffee_shops（咖啡门店表）

  • shop_id：INT，门店编号，主键
  • shop_name：VARCHAR(50)，门店名称
  • city：VARCHAR(20)，所在城市
  • district：VARCHAR(30)，所在区域

表2：order_details（订单明细表）

  • order_id：INT，订单编号，主键
  • shop_id：INT，门店编号，关联 coffee_shops.shop_id
  • customer_name：VARCHAR(30)，顾客姓名
  • product_name：VARCHAR(30)，商品名称
  • order_date：DATE，下单日期
  • quantity：INT，购买数量（保证 >= 1）
  • unit_price：DECIMAL(8,2)，单价（元）

二、问题

请使用链式 LATERAL JOIN（两个 LATERAL 子查询串联，第二个 LATERAL 引用第一个 LATERAL 的输出结果）查询每家门店的王牌产品及该产品的最忠实顾客。

具体规则：
（1）王牌产品：对每家门店，按商品汇总销售总额（SUM(quantity * unit_price)），取销售总额最高的1个商品。若销售总额相同，取总销量（SUM(quantity)）更高的；若仍相同，取 product_name 升序排列靠前的。
（2）最忠实顾客：在确定王牌产品后，对该门店购买过该王牌产品的所有顾客，按购买该产品的总数量（SUM(quantity)）取最多的1位顾客，同时输出该顾客首次购买该产品的日期。若总数量相同，取首次购买日期（MIN(order_date)）更早的；若仍相同，取 customer_name 升序排列靠前的。

输出以下字段：门店名称(shop_name)、所在城市(city)、王牌产品(top_product)、产品销售总额(product_revenue)、最忠实顾客(top_customer)、顾客购买数量(customer_quantity)、首次购买日期(first_purchase_date)。结果按 shop_id 升序排列。若某门店无订单记录则不出现在结果中。

三、示例数据表

coffee_shops 表：

shop_id shop_name city district
1 晨光咖啡·南京西路店 上海 静安区
2 晨光咖啡·望京店 北京 朝阳区

order_details 表：

order_id shop_id customer_name product_name order_date quantity unit_price
1 1 李明 冰美式 2025-03-01 2 22.00
2 1 李明 冰美式 2025-03-05 3 22.00
3 1 王芳 冰美式 2025-03-03 1 22.00
4 1 王芳 拿铁 2025-03-02 2 28.00
5 1 张伟 拿铁 2025-03-04 1 28.00
6 1 张伟 手冲瑰夏 2025-03-06 1 58.00
7 2 赵敏 燕麦拿铁 2025-03-01 3 32.00
8 2 赵敏 燕麦拿铁 2025-03-08 2 32.00
9 2 钱进 燕麦拿铁 2025-03-03 4 32.00
10 2 钱进 美式 2025-03-05 2 20.00
11 2 孙莉 美式 2025-03-02 3 20.00
12 2 孙莉 美式 2025-03-07 1 20.00

四、示例数据查询结果表

说明：

  • 南京西路店产品销售额汇总：冰美式=(2+3+1)×22=132.00，拿铁=(2+1)×28=84.00，手冲瑰夏=1×58=58.00。王牌产品：冰美式(132.00)。
    冰美式顾客购买量：李明=2+3=5，王芳=1。最忠实顾客：李明(5杯，首购03-01)。
  • 望京店产品销售额汇总：燕麦拿铁=(3+2+4)×32=288.00，美式=(2+3+1)×20=120.00。王牌产品：燕麦拿铁(288.00)。
    燕麦拿铁顾客购买量：赵敏=3+2=5，钱进=4。最忠实顾客：赵敏(5杯，首购03-01)。
shop_name city top_product product_revenue top_customer customer_quantity first_purchase_date
晨光咖啡·南京西路店 上海 冰美式 132.00 李明 5 2025-03-01
晨光咖啡·望京店 北京 燕麦拿铁 288.00 赵敏 5 2025-03-01


示例1

输入

CREATE TABLE coffee_shops (
    shop_id INT PRIMARY KEY,
    shop_name VARCHAR(50),
    city VARCHAR(20),
    district VARCHAR(30)
);

CREATE TABLE order_details (
    order_id INT PRIMARY KEY,
    shop_id INT,
    customer_name VARCHAR(30),
    product_name VARCHAR(30),
    order_date DATE,
    quantity INT,
    unit_price DECIMAL(8,2)
);

INSERT INTO coffee_shops VALUES
(1, '晨光咖啡·南京西路店', '上海', '静安区'),
(2, '晨光咖啡·望京店', '北京', '朝阳区');

INSERT INTO order_details VALUES
(1,  1, '李明', '冰美式',   '2025-03-01', 2, 22.00),
(2,  1, '李明', '冰美式',   '2025-03-05', 3, 22.00),
(3,  1, '王芳', '冰美式',   '2025-03-03', 1, 22.00),
(4,  1, '王芳', '拿铁',     '2025-03-02', 2, 28.00),
(5,  1, '张伟', '拿铁',     '2025-03-04', 1, 28.00),
(6,  1, '张伟', '手冲瑰夏', '2025-03-06', 1, 58.00),
(7,  2, '赵敏', '燕麦拿铁', '2025-03-01', 3, 32.00),
(8,  2, '赵敏', '燕麦拿铁', '2025-03-08', 2, 32.00),
(9,  2, '钱进', '燕麦拿铁', '2025-03-03', 4, 32.00),
(10, 2, '钱进', '美式',     '2025-03-05', 2, 20.00),
(11, 2, '孙莉', '美式',     '2025-03-02', 3, 20.00),
(12, 2, '孙莉', '美式',     '2025-03-07', 1, 20.00);

输出

shop_name|city|top_product|product_revenue|top_customer|customer_quantity|first_purchase_date
晨光咖啡·南京西路店|上海|冰美式|132.00|李明|5|2025-03-01
晨光咖啡·望京店|北京|燕麦拿铁|288.00|赵敏|5|2025-03-01
算法知识视频讲解
添加回答
Mysql
ziranfeng头像 ziranfeng 
with t1 as (select shop_id,
product_name as top_product, 
sum(quantity * unit_price) as product_revenue,
row_number() over (partition by shop_id order by SUM(quantity * unit_price) desc, SUM(quantity) desc, product_name) as product_rank
from order_details
group by shop_id, product_name),
t2 as (select od.shop_id, 
od.customer_name as top_customer, 
sum(od.quantity) as customer_quantity,
min(order_date) as first_purchase_date, 
row_number() over (partition by od.shop_id order by SUM(od.quantity) desc, MIN(od.order_date), od.customer_name) as customer_rank
from order_details od 
inner join t1
on od.shop_id = t1.shop_id
where t1.product_rank = 1
and od.product_name = t1.top_product
group by od.shop_id, od.customer_name)

select c.shop_name,
c.city,
t1.top_product,
t1.product_revenue,
t2.top_customer,
t2.customer_quantity,
t2.first_purchase_date
from t1 
inner join t2
on t1.shop_id = t2.shop_id
inner join coffee_shops c 
on t1.shop_id = c.shop_id
where t1.product_rank = 1 and t2.customer_rank = 1

发表于 2026-04-22 10:06:17 回复(0)
牛客982982822号头像 牛客982982822号
select c.shop_name,
c.city,
top_p.product_name as top_product,
top_p.product_revenue as product_revenue,
top_c.customer_name as top_customer,
top_c.customer_quantity as customer_quantity,
top_c.first_purchase_date as first_purchase_date
from coffee_shops c
cross join lateral(
select product_name,
sum(o.quantity*unit_price) as product_revenue
from order_details o
where c.shop_id = o.shop_id
group by product_name
order by product_revenue desc, (sum(o.quantity)) desc
limit 1
) as top_p
cross join lateral(
select customer_name,
sum(o.quantity) as customer_quantity,
min(o.order_date) as first_purchase_date
from order_details o
where c.shop_id = o.shop_id and o.product_name = top_p.product_name
group by customer_name
order by customer_quantity desc, customer_name
limit 1
) as top_c
发表于 2026-04-21 09:53:53 回复(0)
喜欢吃蛋糕蚊不叮说等下个版本吧头像 喜欢吃蛋糕蚊不叮说等下个版本吧

select
c.shop_name
,c.city
,a.product_name top_product
,a.product_revenue
,d.customer_name top_customer
,d.customer_quantity
,d.first_purchase_date
from coffee_shops c
# 每家门店的王牌产品--销售额最高的产品
join lateral (
    select
    shop_id
    ,product_name
    ,sum(quantity*unit_price) product_revenue
    ,sum(quantity) total_quantity
    from order_details o
    where o.shop_id = c.shop_id
    group by shop_id,product_name
    order by product_revenue desc,total_quantity desc,product_name
    limit 1
) a on true
# 该产品的头号顾客--购买数量最多的顾客
join lateral (
    select
    shop_id
    ,customer_name
    ,min(order_date) first_purchase_date
    ,sum(quantity) customer_quantity
    from order_details o
    where o.product_name = a.product_name
          and c.shop_id = o.shop_id
    group by shop_id,customer_name
    order by customer_quantity desc,first_purchase_date,customer_name
    limit 1
) d on true
order by c.shop_id
发表于 2026-04-20 11:07:14 回复(0)
Mysql
牛客816461040号头像 牛客816461040号
-- 门店名称、所在城市、王牌产品、产品销售总额、
-- 最忠实的顾客、顾客售卖数量、首次购买日期

-- 先找各个门店的王牌产品，
with T1 as(
select *,rank()over(partition by T.shop_id order by T.product_revenue
desc,T.product_volume desc) as rk1
from(
select c.shop_name,
c.shop_id,
c.city,
o.product_name,
sum(quantity*unit_price) as product_revenue,
sum(quantity) as product_volume
from order_details o left join coffee_shops c
on o.shop_id=c.shop_id
group by c.shop_id,c.shop_name,o.product_name) as T),

-- 再找到门店、产品维度的忠实顾客,找到最忠实的顾客，和他首次购买日期
T3 as(
select *,
rank()over(partition by T2.shop_id,T2.product_name order by T2.customer_quantity desc,T2.first_purchase_date asc,customer_name asc) as rk
from(
select shop_id,product_name,customer_name,
sum(quantity) as customer_quantity,
min(order_date) as first_purchase_date
from order_details
group by shop_id,product_name,customer_name) as T2)

select T1.shop_name,
T1.city,
T1.product_name as top_product,
T1.product_revenue,
T3.customer_name as top_customer,
T3.customer_quantity,
T3.first_purchase_date
from T1 inner join T3 on T1.shop_id=T3.shop_id and T1.product_name=T3.product_name
where T1.rk1=1 and T3.rk=1
发表于 2026-04-20 09:05:48 回复(0)
陌陌MO头像 陌陌MO
感觉LATERAL用起来好麻烦:<
SELECT
    c.shop_name, c.city,
    top_coffee.product_name top_product,
    top_coffee.product_revenue,
    top_customer.customer_name top_customer,
    top_customer.customer_quantity,
    top_customer.first_purchase_date
FROM coffee_shops c
#选出每家店的top产品
CROSS JOIN LATERAL(
    SELECT
        product_name,
        #总营业额
        SUM(quantity * unit_price) product_revenue
    FROM order_details o
    WHERE
        c.shop_id = o.shop_id
    #由于是对外层每一个shop_id进行统计，排序，因此这里只需要按照product_name分组即可
    GROUP BY product_name
    ORDER BY SUM(quantity * unit_price) DESC, SUM(quantity) DESC, product_name ASC
    LIMIT 1
) top_coffee
#选出每个top产品的最忠实用户（买的最多）
CROSS JOIN LATERAL(
    SELECT
        customer_name,
        #总购买数量
        SUM(quantity) customer_quantity,
        #首次购买日期
        MIN(order_date) first_purchase_date
    FROM order_details o
    WHERE
        #为top_cofee的每个(商店, 咖啡)组合，按照customer统计数量，并且排序
        o.shop_id = c.shop_id
        AND o.product_name = top_coffee.product_name
    #同理，此处只需要按照customer分组即可，shop_id在外层已经分好组了
    GROUP BY customer_name
    ORDER BY SUM(quantity) DESC, MIN(order_date) ASC, customer_name ASC
    LIMIT 1
) top_customer;
发表于 2026-04-19 11:19:07 回复(0)
牛客890344680号头像 牛客890344680号

select shop_name,city,a.product_name as top_product,sum_1 as product_revenue,customer_name as top_customer,customer_quantity,first_purchase_date from (
select coffee_shops.shop_id,shop_name,city,product_name,sum_1 from coffee_shops join lateral (
    select * from (
    select shop_id,product_name,sum(quantity*unit_price) as sum_1,sum(quantity) as sum_q from order_details group by shop_id,product_name)t1
    where t1.shop_id=coffee_shops.shop_id
    order by sum_1 desc,sum_q desc,product_name
    limit 1
)t0 on true)a join lateral (
    select * from (
    select shop_id,customer_name,product_name,min(order_date) as first_purchase_date,sum(quantity) as customer_quantity from order_details
    group by shop_id,customer_name,product_name)t2
    where a.product_name=t2.product_name
    order by customer_quantity desc,first_purchase_date,customer_name
    limit 1
)b on true
发表于 2026-04-12 15:26:36 回复(0)
牛客263927501号头像 牛客263927501号 
with 
#找到王牌产品
    A as
    (select
        shop_id,product_name,
        sum(quantity) as product_quantity,
        sum(quantity*unit_price) as product_revenue
    from
        order_details
    group by
        shop_id,product_name),
#找最忠实客户
    B as
    (select
        shop_id,product_name,customer_name,
        sum(quantity) as customer_quantity,
        min(order_date) as first_purchase_date
    from
        order_details
    group by
        shop_id,product_name,customer_name)
select
    coffee_shops.shop_name,
    coffee_shops.city,
    C.product_name as top_product,
    C.product_revenue,
    D.customer_name as top_customer,
    D.customer_quantity,
    D.first_purchase_date
from
    coffee_shops 
left join lateral
    (select 
        shop_id,product_name,
        product_quantity,
        product_revenue
    from
        A
    where 
        coffee_shops.shop_id=A.shop_id
    order by
        product_revenue desc,
        product_quantity desc,
        A.product_name
    limit 1) C
on
    true
left join lateral
    (select 
        shop_id,product_name,customer_name,
        customer_quantity,
        first_purchase_date
    from
        B
    where 
        coffee_shops.shop_id=B.shop_id
    order by
        customer_quantity desc,
        first_purchase_date,
        customer_name
    limit 1) D
on
    true
完结撒花
发表于 2026-04-12 03:42:17 回复(0)
胖咪是个宝头像 胖咪是个宝
select shop_name, city, tb1.product_name top_product, product_revenue, 
customer_name top_customer, customer_quantity, first_purchase_date
from coffee_shops c 
join lateral(
select shop_id, product_name,
sum(quantity * unit_price) product_revenue
from order_details o
where c.shop_id = o.shop_id
group by shop_id, product_name
order by sum(quantity * unit_price) desc, sum(quantity) desc, product_name
limit 1
) tb1 on TRUE
join lateral(
select shop_id, customer_name, product_name,
sum(quantity) customer_quantity,
min(order_date) first_purchase_date
from order_details o 
where tb1.shop_id = o.shop_id and tb1.product_name = o.product_name
group by shop_id, customer_name, product_name
order by customer_quantity desc, first_purchase_date, customer_name 
limit 1
) tb2 on True
order by c.shop_id;
发表于 2026-04-11 21:31:37 回复(0)
在学c语言的烤冷面很高大头像 在学c语言的烤冷面很高大
select shop_name,
    city,
    t2.product_name as top_product,
    product_revenue,
    customer_name as top_customer,
    customer_quantity,
    first_purchase_date
from coffee_shops cs
-- 计算每个门店、每个产品销售总额并排序
left join lateral (
    select shop_id, product_name,
        sum(quantity* unit_price) as product_revenue,
        sum(quantity) as total_quan,
        row_number()over(partition by shop_id
                            order by sum(quantity* unit_price) desc,
                                    sum(quantity) desc, product_name asc) as rk
    from order_details od
    where od.shop_id = cs.shop_id
    group by shop_id, product_name
) t1 on cs.shop_id = t1.shop_id and t1.rk = 1
-- 根据王牌产品确定每个客户购买量排序
left join lateral(
    select shop_id, product_name, customer_name,
        sum(quantity) as customer_quantity,
        min(order_date) as first_purchase_date,
        row_number()over(partition by shop_id, product_name
                        order by sum(quantity) desc, min(order_date) asc, customer_name asc) as rk2
    from order_details od2
    where od2.shop_id = t1.shop_id and od2.product_name = t1.product_name and t1.rk = 1
    group by shop_id, product_name, customer_name
) t2 on t1.shop_id = t2.shop_id and t2.rk2 = 1
发表于 2026-04-08 10:32:09 回复(0)
像初雪一样遇见你头像 像初雪一样遇见你 
select shop_name,city,product_name as top_product,
total_sales as product_revenue,top_customer,
customer_quantity,first_purchase_date
from coffee_shops cs 
join lateral (
select *
from
(select 
    shop_id,
    product_name,
    sum(quantity*unit_price) as total_sales,
    sum(quantity) as cnt,
    row_number() over(partition by shop_id order by sum(quantity*unit_price) desc,sum(quantity) desc,product_name) as rk
from order_details o 
group by 1,2) e
where e.shop_id=cs.shop_id
and rk=1
) x on true 
join lateral (
select customer_name as top_customer,customer_quantity,first_purchase_date
from 
(select 
    customer_name,
    sum(quantity) as customer_quantity,
    min(order_date) as first_purchase_date,
    row_number() over(order by sum(quantity) desc,min(order_date) asc,customer_name) as rk
from order_details a 
where a.product_name=x.product_name
and a.shop_id=cs.shop_id
group by 1
) g
where rk=1
) b on true
order by cs.shop_id

发表于 2026-04-05 18:15:53 回复(0)
杨桃阿姨头像 杨桃阿姨 
select
    s.shop_name,
    s.city,
    t.top_product,
    t.product_revenue,
    c.top_customer,
    c.customer_quantity,
    c.first_purchase_date
from coffee_shops s
left join lateral(
    select product_name as top_product, sum(quantity * unit_price) as product_revenue
    from order_details o
    where o.shop_id = s.shop_id
    group by product_name
    order by sum(quantity*unit_price)desc, sum(quantity)desc, product_name asc
    limit 1
) t on 1 = 1
left join lateral(
    select
        customer_name as top_customer,
        sum(quantity) as customer_quantity,
        min(order_date) as first_purchase_date
    from order_details o
    where
        o.shop_id = s.shop_id
        and o.product_name = t.top_product
    group by customer_name
    order by customer_quantity desc, first_purchase_date asc, customer_name asc
    limit 1
    )c on 1 = 1
order by s.shop_id asc

发表于 2026-04-04 00:07:42 回复(0)
提交观点

问题信息

难度：
11条回答 110浏览

热门推荐

相关试题

精品咖啡连锁门店王牌产品及其最忠实顾客分析

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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