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

WITH top_product AS(
SELECT
    shop_id,
    shop_name,
    city,
    product_name top_product,
    product_revenue
FROM
(
SELECT
    cs.shop_id,
    cs.shop_name,
    cs.city,
    product_name,
    SUM(quantity*unit_price) product_revenue,
    MIN(order_date) first_purchase_date,
    ROW_NUMBER() OVER(PARTITION BY cs.shop_id, cs.shop_name ORDER BY SUM(quantity*unit_price) DESC, SUM(quantity) DESC,product_name ASC) rk
FROM
    coffee_shops cs RIGHT JOIN order_details od USING(shop_id)
GROUP BY 
    cs.shop_id, cs.shop_name, product_name
) t1
WHERE rk = 1
)

SELECT
    shop_name,
    city,
    top_product,
    product_revenue,
    top_customer,
    customer_quantity,
    first_purchase_date
FROM
(
SELECT
    shop_name,
    ANY_VALUE(tp.shop_id) shop_id,
    ANY_VALUE(city) city,
    ANY_VALUE(tp.top_product) top_product,
    ANY_VALUE(product_revenue) product_revenue,
    customer_name top_customer,
    SUM(quantity) customer_quantity,
    MIN(order_date) first_purchase_date,
    ROW_NUMBER() OVER(PARTITION BY shop_name ORDER BY SUM(quantity) DESC, MIN(order_date) ASC,customer_name ASC) rk
FROM
    top_product tp JOIN order_details od USING(shop_id)
WHERE 
    product_name = top_product
GROUP BY
    shop_name, customer_name
) t2
WHERE
    rk = 1
ORDER BY
    shop_id  ASC