Problem Statement
Give two idioms to select the latest row per customer and discuss trade-offs.
Explanation
First, use ROW_NUMBER over (PARTITION BY customer ORDER BY created_at DESC, id DESC) then filter rn equals one. It is readable and fast with a matching index.
Second, use a join to a subquery that selects MAX(created_at) per customer. It can be concise but may miss ties unless you add a tiebreaker. The window approach is safer and easier to extend.
Code Solution
SolutionRead Only
WITH ranked AS (
SELECT *, ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY created_at DESC, id DESC) rn
FROM orders
)
SELECT * FROM ranked WHERE rn=1;