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

select
    t1.shop_name,
    t1.city,
    t3.product_name as top_product,
    t3.product_revenue,
    t5.customer_name as top_customer,
    t5.customer_quantity,
    t5.first_purchase_date
from
    coffee_shops as t1
    join lateral (
        select
            t2.shop_id,
            t2.product_name,
            sum(t2.quantity * t2.unit_price) as product_revenue
        from order_details as t2
        where t1.shop_id = t2.shop_id
        group by t2.shop_id,t2.product_name
        order by product_revenue desc,sum(t2.quantity) desc,product_name asc
        limit 1
    ) as t3 on true
    join lateral (
        select
            t4.customer_name,
            sum(t4.quantity) as customer_quantity,
            min(t4.order_date) as first_purchase_date
        from order_details as t4
        where t3.product_name = t4.product_name and t1.shop_id = t4.shop_id
        group by t4.customer_name
        order by customer_quantity desc,first_purchase_date desc, t4.customer_name
        limit 1
    ) as t5 on true
order by  t1.shop_id;