Problem Description –
Write an SQL query to find the most recent three orders of each user. If a user ordered less than three orders, return all of their orders.
Return the result table ordered by customer_name
in ascending order and in case of a tie by the customer_id
in ascending order. If there is still a tie, order them by order_date
in descending order.
The query result format is in the following example.


Problem Link – Most recent orders
Difficulty Level – Medium
Solution –
SELECT
name as customer_name,
customer_id,
order_id,
order_date
FROM (
SELECT
c.name,
o.customer_id,
o.order_id,
o.order_date,
DENSE_RANK() OVER(PARTITION BY o.customer_id ORDER BY o.order_date DESC) as rnk
FROM Orders as o JOIN Customers as c
ON o.customer_id = c.customer_id
) x
WHERE rnk < 4
ORDER BY 1, 2, 4 DESC