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
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