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

感觉lateral join使用起来没有特别便捷，大概是我还没摸到门道吧

with order_customer_agg as (
    select
    shop_id,
    product_name,
    customer_name,
    sum(quantity) sum_qty,
    sum(quantity*unit_price) sum_amt,
    min(order_date) min_date
    from order_details
    group by shop_id, product_name, customer_name
),

order_product_agg as (
    select
    shop_id,
    product_name,
    sum(sum_qty) total_quantity,
    sum(sum_amt) total_revenue
    from order_customer_agg
    group by shop_id, product_name
)

select
c.shop_name,
c.city,
p.product_name 	top_product,
p.total_revenue product_revenue,
o.customer_name top_customer,
o.sum_qty customer_quantity	,
o.min_date first_purchase_date
from coffee_shops c
join lateral(
    select
    p.shop_id,
    p.product_name,
    p.total_revenue
    from order_product_agg p
    where c.shop_id = p.shop_id
    order by p.total_revenue desc, p.total_quantity desc
    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, o.customer_name
    limit 1
) o
on true
order by c.shop_id