Skip to content

1532. The Most Recent Three Orders 👍

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
WITH
  OrdersWithRowNumber AS (
    SELECT
      order_id,
      order_date,
      customer_id,
      ROW_NUMBER() OVER(
        PARTITION BY customer_id
        ORDER BY order_date DESC
      ) AS `row_number`
    FROM Orders
  )
SELECT
  Customers.name AS customer_name,
  Customers.customer_id,
  OrdersWithRowNumber.order_id,
  OrdersWithRowNumber.order_date
FROM OrdersWithRowNumber
INNER JOIN Customers
  USING (customer_id)
WHERE `row_number` <= 3
ORDER BY customer_name, customer_id, order_date DESC;