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

# 子查询
select
     o.customer_id,
     customer_name,
     product_name as latest_order
 from
     orders o
     join customers c on o.customer_id = c.customer_id
     join products p on o.product_id = p.product_id
 where
     (o.customer_id, order_date) in (
         select
             customer_id,
             max(order_date)
         from orders
         group by customer_id
     )
 order by o.customer_id asc

# 窗口函数
select 
    customer_id,
    customer_name,
    latest_order
from (
    select 
        o.customer_id,
        customer_name,
        product_name as latest_order,
        row_number() over(partition by o.customer_id order by 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
    group by o.customer_id, customer_name, product_name, order_date
)as a1
where rk = 1
order by customer_id asc