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

with lsb1 as (
    select 
        c.shop_id, 
        shop_name, 
        city, 
        product_name, 
        sum(quantity*unit_price) as product_revenue, 
        sum(quantity) as total_quantity,
        row_number() over(
            partition by shop_name 
            order by sum(quantity*unit_price) desc, sum(quantity) desc, product_name
        ) as rk
    from coffee_shops c
    join order_details o on c.shop_id = o.shop_id
    group by c.shop_id, shop_name, city, product_name
),
lsb2 as (
    select 
        l1.shop_name,
        l1.city,
        l1.product_name as top_product,
        l1.product_revenue,
        o.customer_name,
        sum(quantity) as customer_quantity
    from lsb1 l1 
    join order_details o 
        on l1.shop_id = o.shop_id 
        and l1.product_name = o.product_name
    where l1.rk = 1
    group by 
        l1.shop_name, l1.city, l1.product_name, l1.product_revenue, o.customer_name
)
select 
    shop_name,
    city,
    top_product,
    product_revenue,
    top_customer,
    customer_quantity,
    first_purchase_date
from (
    select 
        l2.shop_name,
        l2.city,
        l2.top_product,
        l2.product_revenue,
        l2.customer_name as top_customer,
        l2.customer_quantity,
        o.order_date as first_purchase_date,
        rank() over(
            partition by l2.shop_name 
            order by l2.customer_quantity desc, 
                     o.order_date asc, 
                     l2.customer_name asc
        ) as rk
    from lsb2 l2 
    join order_details o 
        on l2.customer_name = o.customer_name 
        and l2.top_product = o.product_name
) t 
where t.rk = 1;