You need each customer's three most recent orders. How would you write that query?

To retrieve each customer’s three most recent orders, use a window function that assigns a row number to each order per customer ordered by the order date descending. Example:

SELECT customer_id, order_id, order_date

FROM (

SELECT customer_id, order_id, order_date,

     ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn

FROM orders

) t

WHERE rn <= 3;

This query keeps the top three rows for every customer. It runs in O(n log n) time because of the sorting required for the window function, but it is efficient when you have indexes on customer_id and order_date. An alternative is a correlated subquery that selects the top three order_ids per customer, but the window approach is usually clearer and performs better in modern RDBMS engines. Remember to test the query on a sample dataset and consider adding a covering index if the table is large.