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

WITH order_customer_agg AS (
    SELECT shop_id,
           customer_name,
           product_name,
           SUM(quantity) AS sum_qty,
           SUM(quantity*unit_price) AS sum_amt,
           MIN(order_date) AS min_date
    FROM order_details
    GROUP BY shop_id,customer_name,product_name
),
order_product_agg AS (
    SELECT shop_id,
           product_name,
           SUM(sum_qty) AS total_qty,
           SUM(sum_amt) AS total_amt
    FROM order_customer_agg
    GROUP BY shop_id,product_name
)
SELECT c.shop_name,
       c.city,
       p.product_name AS top_product,
       p.product_revenue,
       o.customer_name AS top_customer,
       o.sum_qty AS customer_quantity,
       o.min_date AS first_purchase_date
FROM coffee_shops c 
JOIN LATERAL(
    SELECT p.shop_id,
           p.product_name,
           p.total_amt AS product_revenue
    FROM order_product_agg p 
    WHERE  c.shop_id=p.shop_id
    ORDER BY p.total_amt DESC,p.total_qty DESC,p.product_name ASC
    LIMIT 1
) p ON true
JOIN LATERAL(
    SELECT o.shop_id,
           o.product_name,
           o.customer_name,
           o.sum_qty,
           o.min_date
    FROM order_customer_agg o 
    WHERE c.shop_id=o.shop_id and p.product_name=o.product_name
    ORDER BY o.sum_qty DESC,o.min_date ASC,customer_name ASC
    LIMIT 1
) o ON true