SQL Interview Questions – The Most Recent Three Orders

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