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

with
    t1 as (
        select
            cs.shop_id,
			cs.shop_name,
            cs.city,
            o.product_name as top_product,
            product_revenue,
            p.customer_name as top_customer,
            customer_quantity,
            first_purchase_date
        from
            coffee_shops cs
            join lateral (
                select
                    product_name,
                    sum(quantity * unit_price) as product_revenue
                from
                    order_details od
                where
                    od.shop_id = cs.shop_id
                group by
                    product_name
                order by
                    sum(quantity * unit_price) desc,
                    sum(quantity) desc,
                    product_name
                limit
                    1
            ) as o on 1
            join lateral (
                select
                    customer_name,
                    sum(quantity) as customer_quantity,
                    min(order_date) as first_purchase_date
                from
                    order_details od
                where
                    od.shop_id = cs.shop_id
                    and od.product_name = o.product_name
                group by
                    customer_name
                order by
                    sum(quantity) desc,
                    min(order_date),
                    customer_name
                limit
                    1
            ) as p on 1
    )
select *
from
    t1
order by
    shop_id