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

with temp0 as (
    select 
        shop_id,
        product_name,
        sum(quantity*unit_price) as total_price,
        sum(quantity) as total_count
    from order_details
    group by shop_id,product_name
), temp1 as (
    select row_number()over(partition by shop_id order by total_price desc,total_count desc,product_name asc) as rk,
        shop_id,product_name
    from temp0
), temp2 as (
    select shop_id,product_name
    from temp1
    where rk = 1
),temp3 as (
    select 
        t2.shop_id,
        t2.product_name,
        customer_name,
        sum(quantity) as customer_total_count,
        min(order_date) as min_order_date
    from temp2 t2 inner join order_details od 
    on t2.shop_id = od.shop_id and t2.product_name = od.product_name
    group by t2.shop_id,t2.product_name,customer_name
), temp4 as (
    select shop_id,product_name,customer_name,customer_total_count,min_order_date,
    row_number()over(partition by shop_id,product_name order by customer_total_count desc,min_order_date asc,customer_name asc) as rk
    from temp3 t3
), temp5 as (
    select shop_id,product_name,customer_name,customer_total_count,min_order_date
    from temp4
    where rk = 1
)

select shop_name,city,t0.product_name as top_product,total_price as product_revenue,customer_name as top_customer,customer_total_count as customer_quantity,
min_order_date as first_purchase_date
from temp5 t5 inner join coffee_shops cs 
on t5.shop_id = cs.shop_id
inner join temp0 t0 on t0.shop_id = t5.shop_id and t0.product_name = t5.product_name
order by t5.shop_id asc;