题解 | 每个顾客最近一次下单的订单信息

/*
这题使用GROUP BY + MAX同样可以达成，但是效率与可读性不如窗口函数，这是窗口函数的基础应用
*/

WITH
t1 AS (     -- 使用窗口函数对每位客户的订单以倒序排名
SELECT
    o.order_id,
    c.customer_id,
    c.customer_name,
    o.order_date,
    ROW_NUMBER() OVER(PARTITION BY c.customer_id ORDER BY o.order_date DESC) AS rk
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.customer_id
)

SELECT      -- 输出每位客户最新订单的信息
    order_id,
    customer_name,
    order_date
FROM t1
WHERE rk = 1
ORDER BY customer_name;