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

WITH a AS(
SELECT 	cs.shop_id, shop_name, city, product_name, product_revenue
FROM coffee_shops cs 
CROSS JOIN LATERAL(
SELECT product_name, SUM(quantity*unit_price) product_revenue, SUM(quantity) quantity_sum
FROM order_details
WHERE shop_id=cs.shop_id
GROUP BY product_name
ORDER BY 2 DESC, 3 DESC, 1
LIMIT 1
) bo1
)

SELECT shop_name, city, product_name top_product, product_revenue, customer_name top_customer, 
    customer_quantity, first_purchase_date
FROM a
CROSS JOIN LATERAL(
SELECT customer_name, SUM(quantity) customer_quantity, MIN(order_date) first_purchase_date
FROM order_details od 
WHERE a.product_name= od.product_name AND a.shop_id = od.shop_id
GROUP BY customer_name
ORDER BY customer_quantity DESC, first_purchase_date, customer_name
LIMIT 1
) bb
ORDER BY shop_id