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

select
    a.shop_name,
    a.city,
    a.product_name as top_product,
    a.gmv as product_revenue,
    b.customer_name as top_customer,
    b.qty as customer_quantity,
    b.first_purchase_date
from
    (
    select
        c.shop_name,
        c.city,
        a.product_name,
        a.gmv,
        c.shop_id
    from
        coffee_shops c
    join lateral(
        select
            product_name,
            sum(quantity*unit_price) as gmv
        from
            order_details o
        where
            o.shop_id = c.shop_id
        group by
            product_name
        order by
            gmv desc,product_name desc
        limit 1
    ) a on true ) a
join lateral (
    select
        customer_name,
        sum(quantity) as qty,
        min(order_date) as first_purchase_date
    from
        order_details o
    where
        o.product_name = a.product_name
    group by
        customer_name
    order by
        qty desc,customer_name
    limit 1
) b on true
order by
    a.shop_id