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

with temp1 as
(
    select shop_id,shop_name,city,product_name,sum_sale as product_revenue,rank() over(partition by shop_name order by sum_sale desc,num desc,product_name asc) as rk from 
(select o.shop_id,shop_name,city,product_name,sum(quantity * unit_price) as sum_sale,sum(quantity) as num from coffee_shops c join order_details o on c.shop_id = o.shop_id group by o.shop_id,shop_name,city,product_name)t
),
temp2 as
(
    select tp1.shop_id,shop_name,city,tp1.product_name,product_revenue,customer_name,quantity,order_date from temp1 tp1 left join order_details o on tp1.shop_id = o.shop_id and tp1.product_name = o.product_name where rk = 1
),
temp3 as
(
    select shop_id,shop_name,city,product_name,product_revenue,customer_name,customer_quantity,first_purchase_date,rank() over(partition by shop_name,city,product_name order by customer_quantity desc,first_purchase_date asc,customer_name asc) as rk from
(select shop_id,shop_name,city,product_name,product_revenue,customer_name,sum(quantity) as customer_quantity,min(order_date) as first_purchase_date from temp2 group by shop_id,shop_name,city,product_name,product_revenue,customer_name)tt
order by shop_id asc
)
select shop_name,city,product_name as top_product,product_revenue,customer_name as top_customer,customer_quantity,first_purchase_date from temp3 where rk = 1 order by shop_id asc