题解 | 每个顾客购买的最新产品名称

WITH last_order AS( --找到by customer的最后一个商品，该表的两列都整理为外键
    SELECT product_id,customer_id
    FROM (
        SELECT 
            *,
            rank() over (partition by customer_id order by order_date DESC) AS rk
        FROM orders
    ) AS t1
    WHERE t1.rk=1
)
SELECT  
    a.*,
    c.product_name AS latest_order
FROM customers AS a
LEFT JOIN last_order AS b
ON a.customer_id=b.customer_id
LEFT JOIN products AS c
ON b.product_id=c.product_id;