一、题目描述
【背景】
某精品咖啡连锁品牌在多个城市开设门店，所有门店共享统一的订单系统。运营团队需要为每家门店找出其"王牌产品"（销售额最高的产品），以及该王牌产品的"最忠实顾客"（购买该产品数量最多的顾客）。该分析需要两步关联：先定位每家门店的王牌产品，再基于该产品进一步找出该产品的头号顾客。
【表结构与字段说明】
表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 |
