𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿:
You have 2 minutes to solve this SQL query.

Find the second most recent order placed by each customer.

Assume the table structure:
orders(order_id, customer_id, order_date)

𝗠𝗲: Challenge accepted! 💪

SELECT
order_id,
customer_id,
order_date
FROM (
SELECT
order_id,
customer_id,
order_date,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC
) AS rn
FROM orders
) ranked
WHERE rn = 2;

💡 Explanation:

The query assigns a rank to each order based on its order date for every customer.

• PARTITION BY customer_id creates a separate ranking for each customer
• ORDER BY order_date DESC ranks the most recent order as 1
• ROW_NUMBER() ensures each order gets a unique rank
• The outer query returns only the order with rn = 2, i.e., the second most recent order

This question tests your understanding of:
✅ Window Functions (ROW_NUMBER)
✅ Ranking Records
✅ Partitioning Data
✅ Top N per Group

🎯 Expected Output Example
Customer ID | Order ID | Order Date
101 | 2056 | 2026-06-15
102 | 2074 | 2026-06-18

Customers with fewer than two orders are automatically excluded.

🚀 Alternative Using a Correlated Subquery
SELECT
o1.order_id,
o1.customer_id,
o1.order_date
FROM orders o1
WHERE 1 = (
SELECT COUNT(*)
FROM orders o2
WHERE o2.customer_id = o1.customer_id
AND o2.order_date > o1.order_date
);

This approach counts how many orders are more recent than the current order. If exactly one order is more recent, the current order is the second most recent.

🚀 Tip for SQL Job Seekers:
Questions involving the Nth latest or Nth earliest record appear frequently in interviews. Practice solving them using:
• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• Correlated Subqueries

Understanding when to use each approach is a valuable interview skill.

❤️ React with ❤️ for more interview challenges!