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

SELECT
    t.customer_id,
    t.customer_name,
    t.product_name as latest_order
FROM
    (SELECT  
        c.customer_id,
        c.customer_name,
        p.product_name,
        RANK() over (partition by c.customer_id ORDER BY o.order_date DESC) as rk
    FROM
        orders o 
        JOIN customers c ON o.customer_id=c.customer_id
        JOIN products p ON o.product_id=p.product_id
    ) t 
WHERE
    rk=1

1. 没说只返回一行，只说返回最新的，用RANK()，否则用ROW_RANK()
2. 也可以直接返回第一行，FIRST_VALUE(p.product_name) OVER (PARTITION BY c.customer_id ORDER BY o.order_date DESC) AS last_product，把窗口函数和外层查询合成一行写