How do you find the most recent order per customer?

Short answer: ROW_NUMBER() PARTITION BY CustomerId ORDER BY OrderDate DESC and filter rn = 1. Alternatively CROSS APPLY TOP 1.

Sample solution

SELECT CustomerId, OrderId, OrderDate, Amount
FROM (
    SELECT *, ROW_NUMBER() OVER (
        PARTITION BY CustomerId ORDER BY OrderDate DESC, OrderId DESC
    ) AS rn
    FROM Orders
) t
WHERE rn = 1;

Add a deterministic tie-breaker (OrderId) when dates collide.