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

select 
    c.shop_name,
    c.city,
    temp1.top_product,
    temp1.product_revenue,
    temp2.top_customer,
    temp2.customer_quantity,
    temp2.first_purchase_date
from coffee_shops c
    join lateral  
    (
        select 
            product_name as top_product,
            SUM(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(quantity) desc,product_name 
        limit 1
    )temp1 on true
    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=c.shop_id and temp1.top_product=o.product_name
        group by customer_name
        order by customer_quantity desc ,first_purchase_date,customer_name 
        limit 1
    )temp2 on true