【字节跳动】链式 LATERAL JOIN 查询门店王牌产品与最忠实顾客

题目描述

来源：字节跳动 SQL 面试真题

某精品咖啡连锁品牌在多个城市开设门店，所有门店共享统一的订单系统。运营团队需要为每家门店找出其“王牌产品”（销售额最高的产品），以及该王牌产品的“最忠实顾客”（购买该产品数量最多的顾客）。

本题要求使用两个串联的 LATERAL JOIN：第一个 LATERAL 子查询定位每家门店的王牌产品，第二个 LATERAL 子查询引用第一个 LATERAL 的产品结果，进一步定位该产品的头号顾客。

数据表

coffee_shops 咖啡门店表

order_details 订单明细表

业务规则

王牌产品

对每家门店按商品汇总销售总额 SUM(quantity * unit_price)，取销售额最高的 1 个商品：

1. 销售总额降序。
2. 销售总额相同时，总销量 SUM(quantity) 降序。
3. 仍相同时，product_name 升序。

最忠实顾客

确定王牌产品后，只统计该门店购买过该产品的顾客，按购买该产品的总数量取最多的 1 位：

1. customer_quantity 降序。
2. 总数量相同时，首次购买日期 MIN(order_date) 升序。
3. 仍相同时，customer_name 升序。

问题

请使用链式 LATERAL JOIN（两个 LATERAL 子查询串联，第二个 LATERAL 引用第一个 LATERAL 的输出结果），输出：

无订单记录的门店不输出，结果按 shop_id 升序排列。

数据样例

当前运行环境

coffee_shopsorder_details期望结果

shop_idPKINT	shop_nameVARCHAR(50)	cityVARCHAR(20)	districtVARCHAR(30)
1	静安精品咖啡	上海	静安区
2	三里屯咖啡实验室	北京	朝阳区
3	湖滨咖啡馆	杭州	上城区
4	太古里咖啡馆	成都	锦江区

输入数据显示 4 / 4 行