Interview Question

Find latest order by customer

Rank orders inside each customer and keep row number 1.

💡 Concept ✅ Quick Revision 🗃️ SQL

Answer

The latest order per customer can be selected by ranking each customer’s orders by time. • PARTITION BY restarts numbering for each customer. • ORDER BY descending places the newest order first. • A unique tie-breaker makes the result deterministic when timestamps match.

💡 SQL Example

SELECT customer_id, order_id, ordered_at FROM (SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC, order_id DESC) AS rn FROM orders o) ranked WHERE rn = 1;

Result

One latest order row per customer

⚡ Quick Revision

Rank orders inside each customer and keep row number 1.

←

PreviousFind average salary by department

NextFind employees joined in last month

→