SQL Interview Questions – The Most Recent Orders for Each Product

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

Solution –

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