Problem Description –
Write an SQL query to find the most recent order(s) of each product.
Return the result table ordered by
product_name in ascending order and in case of a tie by the
product_id in ascending order. If there still a tie, order them by
order_id in ascending order.
The query result format is in the following example.
Problem Link – Most Recent Order
Difficulty Level – Medium
SELECT product_name, product_id, order_id, order_date FROM ( SELECT p.product_name, o.product_id, o.order_id, o.order_date, DENSE_RANK() OVER(PARTITION BY o.product_id ORDER BY o.order_date DESC) as rnk FROM Products as p JOIN Orders as o ON p.product_id = o.product_id ) x WHERE rnk = 1 ORDER BY 1, 2, 3